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!
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? |