Re: pg_dump insert column GENERATED

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"

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

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.

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>