Re: pg_restore - generated column - not populating

The logs don't show errors. I came across something similar here
https://www.postgresql-archive.org/Dumping-restoring-fails-on-inherited-generated-column-td6114378.html

but not sure what the solution is.

On Mon, Feb 22, 2021 at 5:57 PM Santosh Udupi <email(at)hitha(dot)net> wrote:

> I used the following commands for dump
>
> pg_dump -c mydb | gzip -9 > mydb.gz
> pg_dump -C -Fc mydb > mydb.backup
> pg_dump -Ft mydb > mydb.tar
>
> For restore, I created a blank database by issuing the command "createdb
> mydb" and then tried
>
> gunzip -c mydb.gz | psql mydb
> pg_restore -d mydb mydb.backup
> pg_restore -Ft -d mydb mydb.backup
>
> I have tried -c, -C, schema only etc but nothing has worked so far.
>
> I didn't check the Postgres logs. Thanks for the suggestion. I will check
> that.
>
>
>
> On Mon, Feb 22, 2021 at 5:16 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 2/22/21 5:08 PM, Santosh Udupi wrote:
>> > Hi all,
>> >
>> > My database has tables with generated columns. I altered a table and
>> > added a generated column as below:
>> >
>> > alter table billing add primary_bill_to_id int GENERATED ALWAYS as
>> > ((info->>'vp')::int) stored
>> >
>> >
>> > Now, when I do the pg_dump and pg_restore, this column does not get
>> > populated. It remains null
>> >
>> > "Info" is the jsonb column in the table and info->>'vp' has values in
>> > multiple rows but still generated column "primary_bill_to_id" is null
>> > after the restore
>> >
>> > I am using postgres version 13
>> >
>> > Can you tell me what am I missing?
>>
>> Without the commands you used to do the dump and restore it will be
>> difficult to come to any conclusions. It would also be helpful to look
>> at the Postgres logs from the restore to see if there are any error
>> messages.
>>
>> >
>> > Thank you for your help.
>> > Santosh
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>