Re: Insert/Dump/Restore table with generated columns

On Thu, 1 Jul 2021 at 22:06, <zickzack(at)quantentunnel(dot)de> wrote:
> 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.

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