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!
