Thread: Insert/Dump/Restore table with generated columns

Hi,

I have several tables with generated columns. If I restore the plain dumped data (insert statements from pg_dump) I'll
getthe 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
sourcecolumns. 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
willfire 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!

On Thu, 1 Jul 2021 at 22:06, <zickzack@quantentunnel.de> wrote:
> I have several tables with generated columns. If I restore the plain dumped data (insert statements from pg_dump)
I'llget the error message "Column xyz is a generated column.". The exception is understandably, no question (and is
welldocumented). 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
beempty. 

As far as I can see, this shouldn't happen.  I tried to recreate and I can't.

create table ab (a int, b int generated always as (a / 2) stored);
insert into ab values(1);

Running:

pg_dump --table=ab --column-inserts postgres

I see the following in the pg_dump output.

INSERT INTO public.ab (a, b) VALUES (1, DEFAULT);

pg_dump --table=ab --inserts postgres

gives:

INSERT INTO public.ab VALUES (1, DEFAULT);

both of these commands work fine when I run them on the existing database.

I tested this on current master, but looking at the history [1], it
looks like the pg_dump support was added when the feature went in, so
that indicates that it was not missed then subsequently fixed later.

Just to keep us from having to guess, are you able to share the
version of PostgreSQL you're running? Also, the version of pg_dump?
pg_dump --version will tell you that.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30

Hi David,

thanks for you're quick answer.
I'am using postgres 12 and also tested with 13. Both inside the official (debian based) docker hub hosted container.

After you're answer I did a few investigations. If I insert data with a single insert, everything is working like
expected

INSERT INTO public.ab VALUES (1, DEFAULT);

this changes if I do multiple inserts in one statement:

INSERT INTO public.ab VALUES (1, DEFAULT), (2, DEFAULT);
ERROR:  cannot insert into column "b"
DETAIL:  Column "b" is a generated column.

Is this a bug or a feature?

In my specific backup case I exported the data with the flags: --column-inserts, --inserts and --rows-per-insert=1000.
Dueto the latter I ran into the behaviour described obove.
 
If I export the data with the default copy statement, no problems occur.

Greetings

zickzack@quantentunnel.de writes:
> After you're answer I did a few investigations. If I insert data with a single insert, everything is working like
expected
> INSERT INTO public.ab VALUES (1, DEFAULT);
> this changes if I do multiple inserts in one statement:
> INSERT INTO public.ab VALUES (1, DEFAULT), (2, DEFAULT);
> ERROR:  cannot insert into column "b"
> DETAIL:  Column "b" is a generated column.

> Is this a bug or a feature?

It's a deficiency :-(.  It's fixed for v14 but the fix seemed too invasive
to back-patch.

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=17958972f

            regards, tom lane