Re: pg_restore - generated column - not populating

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.

On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver <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>> wrote:
> >
> > Santosh Udupi <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
>