Nicholas Kyriakides Nicholas Kyriakides - 9 days ago 6
SQL Question

Keeping a column in sync with another column in Postgres

I'm wondering if it's possible to have a column always kept in sync with another column in the same table.

Let this table be an example:

+------+-----------+
| name | name_copy |
+------+-----------+
| John | John |
+------+-----------+
| Mary | Mary |
+------+-----------+


I'd like to:

Be able to
INSERT
into this table, using providing a value only for the
name
column - The
name_copy
column should automatically take the value I used in
name


When
UPDATE
-ing the
name
column on a pre-existing row, the
name_copy
should automatically update to match the new & updated
name_column
.

Some solutions




  • I could do this via code but that would be terribly bad as there's no guarantee the data would always be accessible by my code (what if someone changes the data through a DB client?)



What would be a safe and reliable and easy way to tackle this in Postgres?

Answer

Create a trigger. Simple trigger function:

create or replace function trigger_on_example()
returns trigger language plpgsql as $$
begin
    new.name_copy := new.name;
    return new;
end
$$;

Test full example here.