insert column monetary type
Good afternoon. I can't figure out the problem. Digit group group separator causes an insertion error, what should I do? lc_monetary = 'ru_RU.UTF-8' Digit group group separator is a space. Financial type format: 7,649.00 ₽ DELETE FROM ONLY bpd.class_prop_user_small_val WHERE id_class_prop = 74502; Initial version of the data: INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7,649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); -- ERROR: invalid input syntax for type money: "7,649.00 ₽". LINE 6: ..., NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7,649.00 ... Corrected INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); --- INSERT 0 1 Why does a financial type conversion with a legal regional digit group separator cause an error?
pg_dump insert column GENERATED
Good afternoon.
Why does pg_dump generate an insertion script in the generated columns?
This causes insertion errors.
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_inside - column should be excluded how to do it?
INSERT INTO bpd.object (id, id_class, id_position, bquantity,
id_position_root, id_conception, barcode_unit, id_unit_conversion_rule,
"timestamp", on_freeze, timestamp_class, name, id_class_root, id_group,
id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier,
id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84,
202512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19
11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1,
false, 1);
Table:
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)
)
insert column monetary type ver 2
Good afternoon. Sorry about the first example. I can't figure out the problem. Digit group group separator causes an insertion error, what should I do? lc_monetary = 'ru_RU.UTF-8' Digit group group separator is a space. Financial type format: 7,649.00 ₽ DELETE FROM ONLY bpd.class_prop_user_small_val WHERE id_class_prop = 74502; Initial version of the data: INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); -- ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649,00 ... Corrected INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); --- INSERT 0 1 Why does a financial type conversion with a legal regional digit group separator cause an error?
any default columns for tracking / auditing purpose
In Postgres 12 and up, are there any default columns available which could be useful for tracking / auditing purposes ? e.g. - real user who entered / updated the data - timestamp when data was updated / entered where can i get a list of such default columns (if any) - which could be accessed easily in user applications. also what is the usual practice in creating userID's in a large multi-tenanted applications with highly dynamically changing users (e.g students who enroll and go off after some sometime), with all users having the same access - is a different userID created in the system for each user or is a common postgresID used by the application and the application is responsible for tracking individual users with its own userID. (this will reduce system overhead) with warm regards Sanjay
Re: insert column monetary type ver 2
so 20. 11. 2021 v 13:45 odesílatel Дмитрий Иванов napsal: > > Good afternoon. > Sorry about the first example. > I can't figure out the problem. Digit group group separator causes an > insertion error, what should I do? > lc_monetary = 'ru_RU.UTF-8' > Digit group group separator is a space. > Financial type format: 7,649.00 ₽ > DELETE FROM ONLY bpd.class_prop_user_small_val > WHERE id_class_prop = 74502; > Initial version of the data: > INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, > val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, > val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, > id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) > VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, > NULL, NULL, NULL, NULL, '7 649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, > false, false, true); > -- > ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., NULL, > NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649,00 ... > Corrected > INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, > val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, > val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, > id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) > VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, > NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, > false, false, true); > --- > INSERT 0 1 > > Why does a financial type conversion with a legal regional digit group > separator cause an error? I'm not sure what's the problem on your side. But definitely check this link https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money explaining why money is not recommended to be used. Maybe you're facing one of the known problems.
Re: insert column monetary type ver 2
I saw it. I’m not tied down hard on this type. It's just that it exists and I decided to include it in my development. Thanks. сб, 20 нояб. 2021 г. в 19:18, Josef Šimánek : > so 20. 11. 2021 v 13:45 odesílatel Дмитрий Иванов > napsal: > > > > Good afternoon. > > Sorry about the first example. > > I can't figure out the problem. Digit group group separator causes an > insertion error, what should I do? > > lc_monetary = 'ru_RU.UTF-8' > > Digit group group separator is a space. > > Financial type format: 7,649.00 ₽ > > DELETE FROM ONLY bpd.class_prop_user_small_val > > WHERE id_class_prop = 74502; > > Initial version of the data: > > INSERT INTO bpd. class_prop_user_small_val (id_class_prop, > timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, > val_date, val_time, val_interval, val_timestamp, val_money, val_double, > max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, > max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649.00 ₽', NULL, -1, 2, > 6161, 6, false, 0, -1, false, false, true); > > -- > > ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649,00 ... > > Corrected > > INSERT INTO bpd. class_prop_user_small_val (id_class_prop, > timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, > val_date, val_time, val_interval, val_timestamp, val_money, val_double, > max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, > max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, > 6161, 6, false, 0, -1, false, false, true); > > --- > > INSERT 0 1 > > > > Why does a financial type conversion with a legal regional digit group > separator cause an error? > > I'm not sure what's the problem on your side. But definitely check > this link https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money > explaining why money is not recommended to be used. Maybe you're > facing one of the known problems. >
Re: any default columns for tracking / auditing purpose
> On Nov 20, 2021, at 6:50 AM, Sanjay Minni wrote: > > > In Postgres 12 and up, are there any default columns available which could be > useful for tracking / auditing purposes ? > e.g. > - real user who entered / updated the data > - timestamp when data was updated / entered > where can i get a list of such default columns (if any) - which could be > accessed easily in user applications. > > also what is the usual practice in creating userID's in a large > multi-tenanted applications with highly dynamically changing users (e.g > students who enroll and go off after some sometime), with all users having > the same access - > is a different userID created in the system for each user or is a common > postgresID used by the application and the application is responsible for > tracking individual users with its own userID. (this will reduce system > overhead) > Never ever re-use an id. There are lots of them. If you need lots and lots of them use UUID. > with warm regards > Sanjay >
Re: insert column monetary type ver 2
On 11/20/21 04:45, Дмитрий Иванов wrote: Good afternoon. Sorry about the first example. I can't figure out the problem. Digit group group separator causes an insertion error, what should I do? lc_monetary = 'ru_RU.UTF-8' Digit group group separator is a space. Financial type format: 7,649.00 ₽ DELETE FROM ONLY bpd.class_prop_user_small_val WHERE id_class_prop = 74502; Initial version of the data: INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); -- ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649,00 ... Corrected INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar, val_real, val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class, id_data_type, inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false, true); --- INSERT 0 1 Why does a financial type conversion with a legal regional digit group separator cause an error? Because this(cash.c): https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/cash.c;h=d093ce80386f4cf61f3127f3cfe77181f4edfed5;hb=HEAD has this?: /* cash_in() 91 * Convert a string to a cash data type. 92 * Format is [$]###[,]###[.##] 93 * Examples: 123.45 $123.45 $123,456.78 94 * 95 */ The 1000s(group) separator is determined by LC_NUMERIC and I don't believe that is used by the money type. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_dump insert column GENERATED
On 11/20/21 04:27, Дмитрий Иванов wrote: Good afternoon. Why does pg_dump generate an insertion script in the generated columns? This causes insertion errors. 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_inside - column should be excluded how to do it? Actually I think it should be DEFAULT. What is your pg_dump version? It shouldn't unless you are specifying --inserts. What is the full pg_dump command you are using? What are the errors? INSERT INTO bpd.object (id, id_class, id_position, bquantity, id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, 202512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, false, 1); -- Adrian Klaver adrian.kla...@aklaver.com
Re: insert column monetary type ver 2
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes:
> I can't figure out the problem. Digit group group separator causes an
> insertion error, what should I do?
> lc_monetary = 'ru_RU.UTF-8'
On my RHEL8 (moderately recent glibc) platform, that locale's
mon_thousands_sep symbol is not a plain space but "\342\200\257":
p *lconvert
$3 = {decimal_point = 0x23ef120 ".", thousands_sep = 0x23ef140 "",
grouping = 0x23ef160 "", int_curr_symbol = 0x23ef1a0 "RUB ",
currency_symbol = 0x23ef1c0 "\342\202\275",
mon_decimal_point = 0x23ef1e0 ",",
mon_thousands_sep = 0x23ef200 "\342\200\257",
mon_grouping = 0x23ef220 "\003\003", positive_sign = 0x23ef240 "",
negative_sign = 0x23ef260 "-", int_frac_digits = 2 '\002',
frac_digits = 2 '\002', p_cs_precedes = 0 '\000', p_sep_by_space = 1 '\001',
n_cs_precedes = 0 '\000', n_sep_by_space = 1 '\001', p_sign_posn = 1 '\001',
n_sign_posn = 1 '\001', int_p_cs_precedes = 0 '\000',
int_p_sep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
int_n_sign_posn = 0 '\000'}
A quick lookup later, that's U+202F or "narrow no-break space".
cash_in is picky about this, and won't take plain ASCII space as
a substitute. Not sure if it should.
regards, tom lane
Re: pg_dump insert column GENERATED
Adrian Klaver writes: > On 11/20/21 04:27, Дмитрий Иванов wrote: >> Why does pg_dump generate an insertion script in the generated columns? > Actually I think it should be DEFAULT. It should be, and it is when I try this example. I get output like -- -- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: postgres -- INSERT INTO bpd.object (id, id_class, id_position, bquantity, id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, 202512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, DEFAULT, 1); I wonder what version of pg_dump is actually being used there. regards, tom lane
Re: pg_dump insert column GENERATED
Ok, I see. This is actually an interesting question. I don't understand which approach to use. I am using pd_dump and pg_restore of the receiving server. It might make sense to use pg_dump source, pg_restore sink сб, 20 нояб. 2021 г. в 22:33, Tom Lane : > Adrian Klaver writes: > > On 11/20/21 04:27, Дмитрий Иванов wrote: > >> Why does pg_dump generate an insertion script in the generated columns? > > > Actually I think it should be DEFAULT. > > It should be, and it is when I try this example. I get output like > > -- > -- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: postgres > -- > > INSERT INTO bpd.object (id, id_class, id_position, bquantity, > id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, > "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, > id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, > id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, > 202512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 > 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, > DEFAULT, 1); > > I wonder what version of pg_dump is actually being used there. > > regards, tom lane >
Re: pg_dump insert column GENERATED
I don't know. 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" I used the pg_dump version of the receiving server, but the pg_dump of the source server initially gave almost the same result, a COPY error of the empty table PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit to 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 сб, 20 нояб. 2021 г. в 22:00, Adrian Klaver : > On 11/20/21 04:27, Дмитрий Иванов wrote: > > Good afternoon. > > Why does pg_dump generate an insertion script in the generated columns? > > This causes insertion errors. > > 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_inside - column should be excluded how to do it? > > Actually I think it should be DEFAULT. > > What is your pg_dump version? > > It shouldn't unless you are specifying --inserts. > > What is the full pg_dump command you are using? > > What are the errors? > > > > > INSERT INTO bpd.object (id, id_class, id_position, bquantity, > > id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, > > "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, > > id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, > > id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, > > 202512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 > > 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, > > -1, false, 1); > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_dump insert column GENERATED
On 11/20/21 10:33, Дмитрий Иванов wrote: I don't know. 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" I used the pg_dump version of the receiving server, but the pg_dump of the source server initially gave almost the same result, a COPY error of the empty table PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit to 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 To be clear you used the Postgres 14 version of pg_dump to dump from a Postgres 12 version database, correct? What version of pg_restore did you use to restore to the Postgres 14 database? Where did you install the Postgres 12.9 version package from? -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_dump insert column GENERATED
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 : > On 11/20/21 10:33, Дмитрий Иванов wrote: > > I don't know. > > 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" > > I used the pg_dump version of the receiving server, but the pg_dump of > > the source server initially gave almost the same result, a COPY error of > > the empty table > > PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit > > to > > 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 > > To be clear you used the Postgres 14 version of pg_dump to dump from a > Postgres 12 version database, correct? > > What version of pg_restore did you use to restore to the Postgres 14 > database? > > Where did you install the Postgres 12.9 version package from? > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_dump insert column GENERATED
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 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.kla...@aklaver.com
Re: insert column monetary type ver 2
On 11/20/21 11:01 AM, Tom Lane wrote:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes:
I can't figure out the problem. Digit group group separator causes an
insertion error, what should I do?
lc_monetary = 'ru_RU.UTF-8'
On my RHEL8 (moderately recent glibc) platform, that locale's
mon_thousands_sep symbol is not a plain space but "\342\200\257":
p *lconvert
$3 = {decimal_point = 0x23ef120 ".", thousands_sep = 0x23ef140 "",
grouping = 0x23ef160 "", int_curr_symbol = 0x23ef1a0 "RUB ",
currency_symbol = 0x23ef1c0 "\342\202\275",
mon_decimal_point = 0x23ef1e0 ",",
mon_thousands_sep = 0x23ef200 "\342\200\257",
mon_grouping = 0x23ef220 "\003\003", positive_sign = 0x23ef240 "",
negative_sign = 0x23ef260 "-", int_frac_digits = 2 '\002',
frac_digits = 2 '\002', p_cs_precedes = 0 '\000', p_sep_by_space = 1 '\001',
n_cs_precedes = 0 '\000', n_sep_by_space = 1 '\001', p_sign_posn = 1 '\001',
n_sign_posn = 1 '\001', int_p_cs_precedes = 0 '\000',
int_p_sep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
int_n_sign_posn = 0 '\000'}
A quick lookup later, that's U+202F or "narrow no-break space".
cash_in is picky about this, and won't take plain ASCII space as
a substitute. Not sure if it should.
It probably should, based on the Robustness Principle: "be conservative in
what you send, be liberal in what you accept".
--
Angular momentum makes the world go 'round.
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
Re: insert column monetary type ver 2
I think I would love to discuss this topic, but my English won't allow it.
I understand this type is there but the best way to avoid mistakes when
working with it is not to work with it. thank you.
вс, 21 нояб. 2021 г. в 09:02, Ron :
> On 11/20/21 11:01 AM, Tom Lane wrote:
> > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=
> writes:
> >> I can't figure out the problem. Digit group group separator causes an
> >> insertion error, what should I do?
> >> lc_monetary = 'ru_RU.UTF-8'
> > On my RHEL8 (moderately recent glibc) platform, that locale's
> > mon_thousands_sep symbol is not a plain space but "\342\200\257":
> >
> > p *lconvert
> > $3 = {decimal_point = 0x23ef120 ".", thousands_sep = 0x23ef140 "",
> >grouping = 0x23ef160 "", int_curr_symbol = 0x23ef1a0 "RUB ",
> >currency_symbol = 0x23ef1c0 "\342\202\275",
> >mon_decimal_point = 0x23ef1e0 ",",
> >mon_thousands_sep = 0x23ef200 "\342\200\257",
> >mon_grouping = 0x23ef220 "\003\003", positive_sign = 0x23ef240 "",
> >negative_sign = 0x23ef260 "-", int_frac_digits = 2 '\002',
> >frac_digits = 2 '\002', p_cs_precedes = 0 '\000', p_sep_by_space = 1
> '\001',
> >n_cs_precedes = 0 '\000', n_sep_by_space = 1 '\001', p_sign_posn = 1
> '\001',
> >n_sign_posn = 1 '\001', int_p_cs_precedes = 0 '\000',
> >int_p_sep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
> >int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
> >int_n_sign_posn = 0 '\000'}
> >
> > A quick lookup later, that's U+202F or "narrow no-break space".
> >
> > cash_in is picky about this, and won't take plain ASCII space as
> > a substitute. Not sure if it should.
>
> It probably should, based on the Robustness Principle: "be conservative in
> what you send, be liberal in what you accept".
>
> --
> Angular momentum makes the world go 'round.
>
>
>
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 ti
