PostgreSQL Generated Columns by Example | POSETTE: An Event for Postgres 2026

Key Takeaways

Hi everyone. I'm Paulo Melchiorre and today I'm talking to you about the Postgres generated column. I'm doing that using some example and using Django ORM and some beautiful picture from Italy to show you how to use this fantastic feature from Postgres and Django. Okay, so let's dive in in Postgres generated column using some example. But first we have to know what generated column is and when is it been added in Postgres. I found out that generated column has been added in Postgres 12 in 2019, but so it's not as a recent feature, but in the recent Postgres 18 the new virtual um option has been added in it. And this is something that we can see during this this presentation. Unfortunately, I found out reading the Postgres documentation that this is the only example you can find about the generated column. And it's enough to you for understand how you can use generated column, but it's not enough to think about so many different option you can have to use it and you can use it in your project. But before showing you more of this example, I want to present a bit myself. I'm Paulo Melchiorre. I'm Python Django developer. I'm from Italy. I'm on the board member of the Django Software Foundation uh PSF fellow. I organize and I work to organize PyCon Italia, which is this conference that we organize in Italy and will be at the end of May this this year. I also founded Python Pescara and a local community that we organize monthly some tools and some beers. And also I I'm involved in the community trying to help as a navigator in the Django Girls space program and organize various Django Girls workshop. Uh so, as you can understand I try to show you some example of Postgres generated column using Django as as a project. Uh and Django has also a very good documentation. And one part I like the most is this one. We can read in the performance and optimization section uh that's using Django in the right way you have to try to use the lower level if you want to be faster. And speaking of Django and the ORM, the lower level is typically the database. In fact, I like the most using uh in the best way the database. And today we are going to use Django with Postgres, of course. I found out reading about this new function functionality in Postgres generated column and I noticed uh that is something that is computed by the database. So, it's very fast compared with Python. And reading more about it in the internet, I found out also that was something already always updated that requires no trigger, no Python code. So, something very aligned with the principle we just saw before. And after discovering this functionality, I wanted to share with some um other people in the Django community. In this picture, I'm at PyCon D in Germany, specifically in Berlin. And after the pandemic period in 2022, I met Marcus and Mark here in the picture, and I tried to share with them this new functionality and the idea to implement and use it directly from the Django ORM. In fact, they uh give give me some feedback, good feedback about this idea, and I shared also with a wider large community of Django writing directly from the conference and proposing them to add a generated field feature to use the generated column uh in Postgres. And I received a very good feedback. As we do in the Hover community, I opened an issue, and I shared with them my my ideas. We received some some feedbacks and some criticism about what we want to do to use this functionality. And then we moved to the the implementation. As you can notice from the dates on this pull request, it took us a few months to open the pull request, to find all the bugs on the implementation, and to see how good was the ORM layer on top of the generated the columns. And in this process, I had no time or energy to open myself the pull request, but I was involved in the reviewing the pull request and trying to find a lot of edge cases that, as you can see from the conversation, has been a lot. But at the end, we finally merged it in the jungle of 5.0. And just one year after we originally started talking about it. But as for the Postgres documentation, unfortunately also in the Django documentation, which is quite good, this is the only one example that we were able to put in it. Uh the Django documentation as the Postgres one is quite large and I can send it having multiple example for every functionality will be uh difficult or too much for everyone to to read and to and to update. So, I decided to extract all the edge cases and tests I did to help in in the development of the feature and I published it a series of article to explore on my website how to use the generated column with Django, with SQLite, with Postgres specifically, and also with some um geographic functionality using GeoDjango and PostGIS. Before showing you some of these example in the next minutes, I want to quickly share out the layer of the Django ORM using top of generated column. Is the generated field is a new field in the ORM. Uh it rely on the computed value and it rely also also um in on the database to perform this update with no extra code and to have the data uh always in sync. We have some attributes to uh create a generated field. You have to specify the expression that you want to have that calculate the final value that has a lot of different uh implementation. We have to specify the output field, which is the type of the column we are going to create. And we have also to specify if the column is persistent or not. Uh and until Postgres 17, they are all the only way we wanted to use um the generated column was the stored column. But from Postgres 18, now we can also to use um the not persistent, the virtual one, which is quite uh useful uh to perform uh a lot of writing and few reads because the value is calculated when you read it the value and not when you write in your database. Okay, let's start seeing some example here. I want to share with you some picture from Italy where I'm I'm from and this uh the first um the first picture from this building, the center of Rome, which is circular. And since Roman liked a lot the circular form, I created a class that is a circle. Of course. This is a Django model. Uh I tried to use some simple class to show you how to implement some generated field. Uh it has only a radius field, which is a float, and the generated field calculate automatically the area. Uh we are already using some function from the Django ORM, which are at the same time some function in the Postgres layer. Uh we are calculating the the area using some uh functions, some um constant. And also, I put the output field and the persistent value. Uh the string representation for it is only needed to show you the result of this calculation. And this is the SQL code that they here or run from Django we generate for us. Um, it's very similar to what we just saw in the Django layer, but is SQL that used similar function and similar um, and similar variables. If we um, go in the Django shell, we can create directly from the shell. For example, a circle instance uh, giving some input for the radius and we can see directly the the calculated value for the area. Um, it's quite easy to show what has been um, processed and run on and Postgres side, using these these two function. And this is the SQL code that has been um, run by Postgres. We passed value in insert into and we received in return the ID and the area value. So, go and see another picture from Italy. This is a pastry in Venice. We can see on the uh, on the glass here some piece of chocolate with some phrases. So, the next class we are going to see is an item. Uh, I put on this item a price and a quantity. In this case, I also define a default for the quantity, which is a new functionality for from Django 5.2 and and it permit us to use the generated field quite a small different way. So, the expression is quite straightforward and the output is similar to the price field. But the the code we will show in SQL is a little bit different from before. We can see the results so at the fourth and the check on the quantity layer. And the generated column is a multiplication from the price and quantity. But we can use in Django in two different way now. We can specify a price and having the result from the total. But we can also specify quantity directly and this is case the result is different. Let's change city in Italy. This a picture from Bologna is the location when PyCon Italia will be organized this year and this is quite in the center under the two towers in in Bologna. And this is a semaphore and every time I think about semaphore, I think about statuses. I created a class which is an order with two date times. One is nullable and one is mandatory. And I created also generated field to represent the status which is quite simple but it can show us how to use case value and when attributes to calculate the status based on the presence of the two date time here. So the conditional case is quite simple. If we have the payment date time, we can set the status as paid. Otherwise, we use the default value which is created. So let's go and see what is executed at database level. This is the SQL. Um we have the two date time here and the calculation use the same um, function as before we saw in the Django RM. It's very easy to read. If the payment is not null, then the status is paid, otherwise it's created. Let's move with the Django shell. The creation here is straightforward. If we only put the creation date, we have this created status or otherwise in the second example, we have the paid one. Okay, until now we saw very simple example that can work all also in other database like SQLite, but Django has a specific coaching module specifically for Postgres. So, let's activate it. We only have to add this line the settings of our Django project. And after that, we can deep dive in what we can use from that. Uh, this is another picture from um, Venice. It is a uh, Acqua Alta library. It's very old and funny to visit. It's quite crowded with books and it's very difficult to find some information inside the library, but I like this example to tell you about the JSON field. In fact, sometimes we put in JSON field a lot of information which is quite difficult to find. Uh, in this case, I created a package class that help us to represent our packages, our Python packages. Um, we can put the a name on it and also put in the JSON field all the information about the packages that we can retrieve from the Python website. Um, let's see how we can use it. Um, but before we we saw the generated column here, try to get the information key from the info in the data in the JSON values. To retrieve the information, I use Python's standard library to get Django from the pypi.org website, specifying only the name. And then after downloading all the JSON with all the information about the packages, multiple version, we can create the packages passing the JSON. As you can see in the result, we see the Django packages and the latest version of the packages, which is the 6.0.4. Actually, we can implement it in a different way. For example, we can have only a packages with the JSON field and a char field for the name and use a Python property to extract the version from the JSON field, and it works quite well. But if we have this type of implementation, when we retrieve all the packages for our from our database, our column, the table, sorry, and we see that we retrieve all the data. It It means we retrieve a lot of JSON information. It's not required because we only want the version. So, maybe we can implement using an annotation in the Django ORM. And under the hood, we see here in the SQL that we are using only the version extracting directly from the JSON the JSON field. But if we use the generated field, this is the SQL we will have. It's very It's simpler and it's also faster. In fact, I tried to benchmark locally on my web on my computer and with multiple type of packages, you can see from here that a using digital feed is very very fast. This is a logarithmic representation of the differences, but of course it can be different on your computer, but I think more or less you can find that generated feed is quite faster. This is another picture from Venice and communicate me the union from people and I want to show you some implementation of a class with using concatenation. A simple example for a person with the first and last name and with the generated field, we can concatenate the two of them using also a space in the middle and the result is here, a concatenation of this string. The final example is quite straightforward and it's not so complicated. But we can also use concatenation to create something more complex. For example, we can create from a person class a JSON representation and change maybe the key and the name or the content using uh upper case or concatenating only the first and last name. In this case, we have an initial. This is what happened under the hood in SQL and this is what we'll see if we try to create and represent a person. As you can see, it's quite different from before and these are different way to represent it and to have a JSON representation directly in the database. But move on to Milan, another city in Italy. This is the one of the oldest uh library uh in Milan. It's full of books and it will be interesting to try to read everything on it. I created a new class of quotes where we can store the author name and some quotes and text from from the quotes. What I try to do here is to search on it and to create a generated field which contain a search vector to perform some full text search on it. In this example we are trying to create a quotes from Plato and we can see that after creating it we can also list the search vector which removes some stop words and common words from the from the sentence. And if you perform a full text search using a plural and in having in the text a singular form of the same words, we succeed and we have our results. We can decide that also that having the books in different languages we can define the languages in a field and different field and we use it in the search vector. And this is the SQL code that we have, but if we perform it we will have a error. As we can read directly from the error, the problem is the expression is not immutable. So to avoid doing that I use the case and when function that we just saw before and I put every languages I want to use here and this is actually what we are using right now to avoid the immutable limits in the Django website search. As you can see now we have different quotes in different languages and in every cases we have a representation on the vector field also in the case that there There some languages not supported by Postgres, like Latin. This is a beautiful hotel in the Como Lake in the northern Italy. And to book here, you need of course a booking class. And this class we have only the start and date field, but we can generate with using a data range function, data range field. It's not implemented already in the Django ORM, but we can create it this way. And then we can see how it's represented in SQL code. >> [snorts] >> To use it, we can create booking object and then we can see that it's right forward to filter all the bookings using directly a date and using the functionality of the range. We have moved to another city in Italy. This is Trieste in the in the Miramare Castle. We can see this globe. We can use also GeoDjango and its geographic functionality just activating this in the setting the country JS. This is one of the oldest map in in Italy that represents the new worlds and it's quite interesting how many things we can represent. Using for example GeoDjango, we can represent a city with a name a point and we can calculate values thing using field starting from the point field. We can use PostGIS and GeoHash to represent the hash information from a point, like in this way. And creating a city, you can see the GeoHash which can have multiple application. We can also create as before with some concatenation the GeoJSON representation for the city. And using the PostGIS function under the hoods, this is what we get. Only defining the point, we can have a GeoJSON representation that can be stored in the database. But since we are speaking about maps, we have to move away from Italy, and this is a picture from the straightest um uh the straightest road in Australia. It's 90 mi long and straight. And to represent it, we we only need a line and a string. In this case, we have a route, which has a name and a line. And we can kind calculate the length of the line using the generated field in this way. As before, it's used uh PostGIS function, and if we put the the string and the representation of the route, we have the um the calculation of the kilometers in this case of the of the route. Okay, let's jump the world, and now we can go in Colorado. This is a beautiful picture of the state. In a while, we'll I will say you why I took this picture. Uh to represent a state, because we can define a state border using a polygon. And using a generated field to calculate the area of this polygon, which is the state representation. Under the hood, the SQL used the PostGIS function for to calculate the area, and this is the square kilometer for for the Colorado state in US. I choose this one because more or less it's a rectangle. It's very uh very simple representation of the state. It's not exactly a rectangle, but more or less it represent it. So, I used to uh the I used the genetic column because it's very simple and has consistency and you can use directly from the database. You can use directly in Django but not only in Django and it gave gave you cleaner and safer models and it works also it works also in different database in different back ends and is very great for real world apps. My my presentation is released with the creative common license so you can download it from our website. And you can find my contact here the QR code gets you to the my website paloks.net and to to finish this talk I will show you the picture I used in so many backgrounds the which is a is beautiful lakes in the northern Italy in South Tyrol. Thanks all for listening.