Search Postgresql Archives

Re: pg_restore - generated column - not populating

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxx> 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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux