Insert/Dump/Restore table with generated columns

From: zickzack(at)quantentunnel(dot)de
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Insert/Dump/Restore table with generated columns
Date: 2021-07-01 10:06:47
Message-ID: trinity-340020e9-5368-4228-8896-3a3efddead81-1625134007426@3c-app-gmx-bap01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have several tables with generated columns. If I restore the plain dumped data (insert statements from pg_dump) I'll get the error message "Column xyz is a generated column.". The exception is understandably, no question (and is well documented). In case of the error no insert takes place. 
My problem now is that my simple backup/restore workflow is corrupted, cause those tables with generated column will be empty.

## The question is:

Does some "simple" workaround exists to prevent this?
Is it somehow possible to dump only non-generated columns?
Is it somwhow possible to ignore the error and allow the other columns to be inserted?
Any other handy solutions? 

## simple example 

```sql
CREATE OR REPLACE FUNCTION generate_person_age(birth timestamptz) RETURNS double precision
LANGUAGE sql IMMUTABLE
AS $$
select EXTRACT(years FROM justify_interval(now() - birth));
$$; 
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id serial primary key
,name text NOT NULL
,birth timestamptz NOT NULL
,age double precision GENERATED ALWAYS as (generate_person_age(birth)) STORED
); 
insert into person(name, birth) values
('Peter Pan', '1902-01-01'); 
-- leeds to exception
insert into person(id, name, birth, age) values
(1, 'Peter Pan', '1902-01-01 00:00:00+00', '121');
``` 

* exception
```
cannot insert into column "age"
Column "age" is a generated column.
``` 

## Solution ideas

* A colleague had the idea to change the generated columns into a normal one and to use a trigger on changes of the source columns. Inserts should not be different than a default column.
* Writing a before insert trigger, that removes the generated column values. But seems not that performant, cause this will fire on every insert, not only in restores.
* Excluding tables with generated columns during pg_dump. Exporting tables with generated columns with hand written sql. Seems like a good source of many bugs and a maintainance problem. 
Greetings!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Rowley 2021-07-01 13:14:12 Re: Insert/Dump/Restore table with generated columns
Previous Message W.P. 2021-07-01 08:56:05 Damaged (during upgrade?) table, how to repair?