Re: pg_restore - generated column - not populating

If I backup using pgAdmin, I am able to restore using pg_restore but for
some reason, pg_rsestore on the output from pg_dump does not create values
for the generated columns

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

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