by Craig Hafer on June 20, 2024

In this post I want to take a look at Generated Columns which was introduced in Postgres 12.

Well, a generated column is a special kind of column that is generated from other columns. It's the same thing as table views, but for columns.

There are 2 different kinds of generated columns. They can be either virtual or stored. Virtual columns are always computed when they are read and takes up no storage space. Stored columns is computed when it is written and then takes up storage. However, it is worth noting that Postgres only supports stored generated columns.

For this example, we will be creating a table to keep track of some student information.

create table student (
  student_id serial primary key,
  first_name varchar,
  last_name varchar,
  full_name varchar generated always as (first_name || ' ' || last_name) stored,
  age numeric
)
;

As you can see here, full_name is a generated column that is always "first_name last_name".

So, let's test this out by inserting a record.

insert into student (
  first_name,
  last_name,
  age
) values (
  'Joe',
  'Hashrocket',
  16
)
;

And now let's verify that we have a generated full_name:

select * from students;

And you should end up with something like this:

Most people probably don't care when it computes, just as long as it actually does compute. Well, I was curious so I figured I would share my findings with you, in case you were curious.

So, obviously it gets computed when we create the record. But, does it recompute every time the record changes? or is it smart enough to only change when the columns it relies on changes? At what point in the process does it compute?

To investigate this, I went ahead and added some counters onto the table that will keep track of every time certain fields gets updated. I also went ahead and added a before trigger that will increment the counters every time the record is updated.

-- Add the counters to the table
alter table students
add column last_name_counter int default 0,
add column full_name_counter int default 0,
add column age_counter int default 0
;

-- Create the trigger functions
create or replace function increment_last_name_counter() returns trigger AS $$
begin
  NEW.counter := NEW.last_name_counter + 1;
  return NEW;
end;
$$ language plpgsql;

create or replace function increment_full_name_counter() returns trigger AS $$
begin
  NEW.counter := NEW.full_name_counter + 1;
  return NEW;
end;
$$ language plpgsql;

create or replace function increment_age_counter() returns trigger AS $$
begin
  NEW.counter := NEW.age_counter + 1;
  return NEW;
end;
$$ language plpgsql;

-- Create the triggers
create trigger update_last_name_counter
before update OF last_name ON students
for each row
execute function increment_last_name_counter();

create trigger update_full_name_counter
before update OF full_name ON students
for each row
execute function increment_full_name_counter();

create trigger update_age_counter
before update OF age ON students
for each row
execute function increment_age_counter();

Now that we have set up the counters, let's take a look at our sample record:

Now, let's change the last_name:

update students SET last_name = 'Smith' WHERE student_id = 1;

As we can see, this incremented the last_name and the full_name by 1. As we would expect.

Now, let's change the first_name:

update students set first_name = 'Matt' where student_id = 1;

As we can see, this incremented just the full_name by 1.

Well, what happens if we change the last_name to the same value it already is? Does that perform an update to either the last_name or the full_name?

update students set last_name = 'Smith' where student_id = 1;

As we can see, this did still update the last_name and the full_name.

For our last test, what happens if we update a field unrelated to the generated column?

update students set age = 24 where student_id = 1;

As we can see, this updated the age_counter but it also updated the full_name_counter. So, this confirms for us that full_name is being computed every time the record is being updated!

So, now we have a simple generated column and we understand a bit more about what is going on "under the hood". But are there any other limitiations or things we should be aware of?

alter table students
add column student_info varchar generated always as (full_name || ' ' || age) stored
;

After running that we will see the following:

Alas, we are restricted here and we can't reference a generated column from another. Which makes sense when you think about it, because things could get weird and you could easily run into some looping logic.

For lack of a better example, let's create a generated column computed from the student's age and last_name with a uniqueness constraint:

alter table students
add column age_last_name varchar generated always as (age || '-' || last_name) stored unique
;

Now, let's assume we have our original example:

And we want to add in his twin sister:

insert into students (first_name, last_name, age) values ('Jane', 'Hashrocket', 16)
;

We will be presented with the following error:

Today we learned about generated columns. We learned that they are a nice way to compute data on the fly, on the database level. We also learned that they are computed every time the record is inserted/updated, whether or not the updated fields affect the generated column. Lastly, we learned that they behave just like regular columns, for the most part.