Re: pg_restore - generated column - not populating

Yes, this is what we have been doing now:- Backup using pg_dump, create
the new database at the destination, manually create the tables which give
problems, and then do the pg_restore. Another solution for us is to backup
using pg_dump that comes with pgAdmin (Windows), rsync it to the
destination server and then do the pg_restore on the server.

On Tue, Feb 23, 2021 at 3:21 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/23/21 12:57 PM, Santosh Udupi wrote:
> > So that it makes it a lot easier for the application logic just to
> > collect json fields and update in one column "info" instead of including
> > multiple columns in the insert/update statements.
>
> I doubt it, but then again this why I don't answer Postgres/JSON SO
> questions anymore. Trying to apply logic to the contortions people go to
> make their life more difficult left me with headaches. At any rate this
> is getting off-topic for the the dump/restore issue you have. When you
> look at the restored tables in each variation of the restore database do
> they look the same as below? If you drop the table in the problem
> database and then recreate it using the script below and then populate
> it with data does it work?
>
>
> >
> > On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 2/23/21 12:15 PM, Santosh Udupi wrote:
> > > Here is my table structure. I will try to get the pg_dump output
> for
> > > this table in both the versions.
> > >
> > > create table tbl_main(
> > >
> > > item_id int GENERATED ALWAYS AS IDENTITY,
> > > -----------------------------------------------------
> > > operating_offices int [] GENERATED ALWAYS AS (
> > > nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
> > > -----------------------------------------------------
> > > primary_bill_to_id int GENERATED ALWAYS as
> ((info->>'vp')::int)
> > > stored ,
> > > ----------------------------------------------
> > > item_status_array text [] GENERATED ALWAYS as ( array[
> > > coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
> > > -------------------------------------------------
> > > info jsonb
> > > ------------------------------
> > > ,is_complete bool GENERATED ALWAYS as (coalesce(
> > > (info->>'lf')::bool = true or (info->>'lg')::bool = true,
> false))
> > > stored
> > > --------------------------------------------
> > > ,is_deleted bool GENERATED ALWAYS as ( coalesce(
> > > (info->>'cv')::bool, false) ) stored
> > > ------------------------------
> > > ,is_a_template bool GENERATED ALWAYS as ( coalesce(
> > > (info->>'cw')::bool, false) ) stored
> > > -------------------------------------------
> > > ,created_by_user_id int
> > > ,created_on timestamptz default now()
> > > ----------------------------------
> > > ,primary key(item_id,created_on )
> > >
> > >
> > > ) partition by range (created_on) ;
> >
> > Which generates(pun intended) the question, why? You are
> deconstructing
> > info into its component parts after the fact, why not just input the
> > data directly into the fields.
> >
> > >
> > >
> ---=================================================================
> > > -- *** index
> > >
> > > CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
> > >
> > >
> > >
> ---=================================================================
> > > -- **** partitions
> > >
> > > -- default partition
> > > create table tbl_main_partition_default
> > > partition of tbl_main default;
> > >
> > > create table tbl_main_partition_2021
> > > partition of tbl_main
> > > for values from ('2020-01-01') to ('2022-01-01');
> > >
> > > create table tbl_main_partition_2022
> > > partition of tbl_main
> > > for values from ('2022-01-01') to ('2023-01-01');
> > >
> > > create table tbl_main_partition_2023
> > > partition of tbl_main
> > > for values from ('2023-01-01') to ('2024-01-01');
> > >
> > >
> ---=================================================================
> > >
> > >
> > >
> > > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>> wrote:
> > >
> > > Santosh Udupi <email(at)hitha(dot)net <mailto:email(at)hitha(dot)net>
> > <mailto:email(at)hitha(dot)net <mailto:email(at)hitha(dot)net>>> writes:
> > > > Both are different versions
> > > > The following works:
> > > > Version: pg_dump (pgAdmin Windows) version:13.1
> > >
> > > > The following does not work: (Does not populate the
> > generated column
> > > > values)
> > > > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)
> > version 13.2
> > >
> > > Hmm ... well, that would be a regression, but you're going to
> > have
> > > to show us how to reproduce it. I'm thinking there must be
> > something
> > > odd about the way the table is declared.
> > >
> > > regards, tom lane
> > >
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>