Re: pg_restore - generated column - not populating

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@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Santosh Udupi <email@hitha.net <mailto:email@hitha.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.klaver@aklaver.com