Re: pg_dump insert column GENERATED

On 11/20/21 20:39, Дмитрий Иванов wrote:
> Yes and yes.
> I filled some tables with GENERATED fields as follows:
> "C:\Program Files\PostgreSQL\12\bin\pg_dump" --file 
> "D:\UPLoad\-=PG-Uchet=-\Base\bpd.sql" --host "127.0.0.1" --port "5999" 
> --username "back" --no-password --verbose --format=p 
> --quote-all-identifiers --column-inserts --inserts --encoding="UTF8" 
> --schema "bpd" "Uchet"
> pg_dump (PostgreSQL) 12.9

> 
> sudo /usr/lib/postgresql/14/bin/psql --file "/home/dismay/uchet/bpd.sql" 
> --host "127.0.0.1" --port "5432" --username "back" --no-password 
> --dbname "postgres" 2> "/home/dismay/uchet/bpd.log"
> psql (PostgreSQL) 14.1 (Debian 14.1-1.pgdg110+1)
> 
> 
> INSERT INTO bpg.object (create in version 10)
> cannot insert a non-DEFAULT value into column "is_inside"

If I am following the bpg.object table was originally created in 
Postgres 10, correct?

Postgres 10 did not have GENERATED ALWAYS AS, that appeared in Postgres 12.

So did you do an ALTER TABLE ... GENERATED ALWAYS AS on bpg.object in 
the Postgres 12 version of the database?


> 
> INSERT INTO bpd.plan_calendar (create in version 12)
> OK

bpd.plan_calendar was created in the Postgres 12 instance of the 
database with GENERATED ALWAYS AS, correct?

> 
> PostgresSQL server history:
> Windows 10 build EDB PostgreSQL 12.9, compiled by Visual C++ build 1914, 
> 64-bit
> 10.х ->pg_upgrade(12)->12.x (I can't remember exactly, I don't want to lie.)
> 
> LINUX DEBIAN 11 (VirtualBOX ORACLE)
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, 
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> 
>  >Is it a promoted replica?
> I am not familiar with this term.

Means was the instance the standby in a replication setup that was then 
moved up(promoted) to the primary.


Are the below from the bpd.sql file?

> 
> CREATE TABLE IF NOT EXISTS bpd.object
> (
>      id bigint NOT NULL DEFAULT 
> nextval('bpd.object_general_id_seq'::regclass),
>      id_class bigint NOT NULL,
>      id_position bigint NOT NULL DEFAULT '-1'::integer,
>      bquantity numeric NOT NULL,
>      id_position_root bigint NOT NULL,
>      id_conception bigint NOT NULL,
>      barcode_unit bigint NOT NULL DEFAULT 0,
>      id_unit_conversion_rule integer NOT NULL,
>      "timestamp" timestamp without time zone NOT NULL DEFAULT 
> LOCALTIMESTAMP,
>      on_freeze boolean NOT NULL DEFAULT false,
>      timestamp_class timestamp without time zone NOT NULL DEFAULT 
> LOCALTIMESTAMP(3),
>      name character varying(255) COLLATE pg_catalog."default" NOT NULL,
>      id_class_root bigint NOT NULL,
>      id_group bigint NOT NULL,
>      id_group_root bigint NOT NULL,
>      id_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
>      "desc" character varying(2044) COLLATE pg_catalog."default" NOT 
> NULL DEFAULT 'н/д'::character varying,
>      id_class_prop_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
>      id_pos_temp_prop bigint NOT NULL DEFAULT '-1'::integer,
>      is_inside boolean GENERATED ALWAYS AS (((id_object_carrier > 0) OR 
> (id_pos_temp_prop > 0))) STORED,
>      mc numeric NOT NULL DEFAULT 0,
>      CONSTRAINT object_pkey PRIMARY KEY (id),
>      CONSTRAINT unique_id_object_id_object_prop UNIQUE (id, 
> id_class_prop_object_carrier),
>      CONSTRAINT lnk_class_snapshot_object FOREIGN KEY (id_class, 
> timestamp_class)
>          REFERENCES bpd.class_snapshot (id, "timestamp") MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT lnk_conception_object FOREIGN KEY (id_conception)
>          REFERENCES bpd.conception (id) MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT lnk_position_object FOREIGN KEY (id_position)
>          REFERENCES bpd."position" (id) MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT check_self_integration CHECK (id <> id_object_carrier)
> )
> 
> TABLESPACE pg_default;
> 
> CREATE TABLE IF NOT EXISTS bpd.plan_calendar
> (
>      id bigint NOT NULL DEFAULT 
> nextval('bpd.work_calendar_id_seq'::regclass),
>      work_date date NOT NULL,
>      work_year integer NOT NULL GENERATED ALWAYS AS 
> (date_part('year'::text, work_date)) STORED,
>      work_month integer NOT NULL GENERATED ALWAYS AS 
> (date_part('month'::text, work_date)) STORED,
>      work_month_day integer NOT NULL GENERATED ALWAYS AS 
> (date_part('day'::text, work_date)) STORED,
>      day_type bpd.day_type NOT NULL DEFAULT 'working'::bpd.day_type,
>      work_year_day integer GENERATED ALWAYS AS (date_part('doy'::text, 
> work_date)) STORED,
>      week40_day numeric,
>      week40_month numeric,
>      week39_day numeric,
>      week39_month numeric,
>      week36_day numeric,
>      week36_month numeric,
>      week35_day numeric,
>      week35_month numeric,
>      week33_day numeric,
>      week33_month numeric,
>      week30_day numeric,
>      week30_month numeric,
>      week24_day numeric,
>      week24_month numeric,
>      week20_day numeric,
>      week20_month numeric,
>      week18_day numeric,
>      week18_month numeric,
>      range_night_part1 tsrange GENERATED ALWAYS AS 
> (tsrange((work_date)::timestamp without time zone, (work_date + 
> '06:00:00'::interval), '[]'::text)) STORED,
>      range_night_part2 tsrange GENERATED ALWAYS AS (tsrange((work_date + 
> '22:00:00'::interval), (work_date + '24:00:00'::interval), '[)'::text)) 
> STORED,
>      work_week_day integer GENERATED ALWAYS AS 
> (date_part('isodow'::text, work_date)) STORED,
>      work_week_day_name character varying COLLATE pg_catalog."default" 
> GENERATED ALWAYS AS (
> CASE date_part('isodow'::text, work_date)
>      WHEN 1 THEN 'ПН'::character varying
>      WHEN 2 THEN 'ВТ'::character varying
>      WHEN 3 THEN 'СР'::character varying
>      WHEN 4 THEN 'ЧТ'::character varying
>      WHEN 5 THEN 'ПН'::character varying
>      WHEN 6 THEN 'СБ'::character varying
>      WHEN 7 THEN 'ВС'::character varying
>      ELSE 'ПН'::character varying
> END) STORED,
>      work_week_day_name_full character varying COLLATE 
> pg_catalog."default" GENERATED ALWAYS AS (
> CASE date_part('isodow'::text, work_date)
>      WHEN 1 THEN 'Понедельник'::character varying
>      WHEN 2 THEN 'Вторник'::character varying
>      WHEN 3 THEN 'Среда'::character varying
>      WHEN 4 THEN 'Четверг'::character varying
>      WHEN 5 THEN 'Пятница'::character varying
>      WHEN 6 THEN 'Суббота'::character varying
>      WHEN 7 THEN 'Воскресенье'::character varying
>      ELSE 'Понедельник'::character varying
> END) STORED,
>      work_year_week integer GENERATED ALWAYS AS (date_part('week'::text, 
> work_date)) STORED,
>      week40_week numeric,
>      week39_week numeric,
>      week36_week numeric,
>      week35_week numeric,
>      week33_week numeric,
>      week30_week numeric,
>      week24_week numeric,
>      week20_week numeric,
>      week18_week numeric,
>      CONSTRAINT plan_calendar_pkey PRIMARY KEY (id),
>      CONSTRAINT unique_plan_calendar UNIQUE (work_date)
> )
> 
> TABLESPACE pg_default;
> 
> вс, 21 нояб. 2021 г. в 06:38, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 11/20/21 17:11, Дмитрий Иванов wrote:
>      > Yes and yes.
>      > I ended up using the pg_dump of the receiving server.
>      >
>      > sudo /usr/lib/postgresql/14/bin/pg_dump --file
>      > "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999"
>      > --username "back" --no-password --verbose --format=c
>      > --quote-all-identifiers --blobs  --column-inserts --inserts --create
>      > --disable-triggers  --encoding="UTF8" "Uchet"
>      >
>      > sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
>      > "5432" --username "back" --no-password --dbname "Uchet"
>      > --disable-triggers --format=c --create --verbose
>      > "/home/dismay/uchet/Uchet.backup"
>      >
>      > sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
>      > "5432" --username "back" --no-password --dbname "Uchet"
>      > --disable-triggers --table="bpd.object" --format=c --verbose
>      > "/home/dismay/uchet/Uchet.backup"
>      >
>      > Receiving server:
>      > PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu,
>      > compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
>      >
>      > Server source:
>      > PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
>      > EDB assembly installed from "Application Stack Builder"
>      >
>      > вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
>     Hmm. I cannot replicate, though in my case both servers(12.9, 14.1) are
>     one same Linux machine.
> 
>     What is the history of the database in the 12.0 instance?
> 
>     Was it upgraded from another instance?
> 
>     If so dump/restore or pg_upgrade?
> 
>     Is it a promoted replica?
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com