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.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxxxx
> <mailto:tgl@xxxxxxxxxxxxx>> wrote:
>
> Santosh Udupi <email@xxxxxxxxx <mailto:email@xxxxxxxxx>> 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@xxxxxxxxxxx
