Re: Creation of FK without enforcing constraint for existing data
Hello, I need to create a Foreign Key for a table without enforcing the constraint for existing data. Few orphan exists in existing data, which we plan to resolve it later. We use the following query to create the FK [ Which of course checks for the presence of record in referencing table] ALTER TABLE public.table1 ADD CONSTRAINT "constraint1_FK" FOREIGN KEY (field1) REFERENCES public.tabnle2(field2) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT; The facility NOT to check for the presence of the parent record is available in SQL Server. Searching an equivalent option in PostGreSQL. Is it available? Else any other way to achieve this. Happiness Always BKR Sivaprakash
Re: Creation of FK without enforcing constraint for existing data
On 2 Aug 2022, at 17:14, sivapostg...@yahoo.com wrote: Hello, I need to create a Foreign Key for a table without enforcing the constraint for existing data. Few orphan exists in existing data, which we plan to resolve it later. We use the following query to create the FK [ Which of course checks for the presence of record in referencing table] Try something like [sql-altertable.html](https://www.postgresql.org/docs/current/sql-altertable.html): ALTER TABLE public.table1 ADD CONSTRAINT "constraint1_FK" FOREIGN KEY (field1) REFERENCES public.tabnle2(field2) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED ; The facility NOT to check for the presence of the parent record is available in SQL Server. Searching an equivalent option in PostGreSQL. Is it available? Else any other way to achieve this. No knowledge of SQL Server and how this is specified there, but the …DEFFER… syntax is according to the SQL standard Regards Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920
Strange collation names ("hu_HU.UTF-8")
Dear Members!
Today we found strange database collation names in a server (V11).
select -- datname,
distinct datcollate
from pg_database
order by datcollate --, datname;
"hu_HU.UTF-8"
"hu_HU.UTF8"
"hu_HU.utf8"
The PGAdmin also gives us these possible collations in the dialog.
Some of the databases were migrated from 9.6 by Python script, and we used
"hu_HU.UTF-8"
to create the empty databases before restoring them.
What I don't understand, that if I query for collations, I got only this:
SELECT *
FROM pg_collation
where upper(collname) like '%HU%' -- and upper(collname) like '%UTF%'
order by collname
hu_HU.utf8
This collation ("hu_HU.UTF-8") doesn't cause any problems, and PGSQL V11
accepts it.
The whole problem appeared when we wanted to copy a database to a new (with
defining the old as template).
*Error: new collation (hu_HU.utf8) is incompatible with the collation of
the template database (hu_HU.UTF-8)*
So maybe the UTF-8 isn't valid but the PG accepted that???
Or is it valid and inherited from 9.6?
Please help me a little bit! Thank you!
Best regards
dd
Re: Syntax error when combining --set and --command has me stumped
If you can use bash, or set up some redirections from whatever you're using to execute ``psql``, you can do:: $ psql somedb --set num=42 <<<'select :num' Timing is on. Expanded display is used automatically. Line style is unicode. Border style is 2. ┌──┐ │ ?column? │ ├──┤ │ 42 │ └──┘ (1 row) Time: 0.517 ms or (more classically):: echo 'select :num' | psql somedb --set num=42 -- Dakkar - GPG public key fingerprint = A071 E618 DD2C 5901 9574 6FE2 40EA 9883 7519 3F88 key id = 0x75193F88
Re: Syntax error when combining --set and --command has me stumped
On 7/29/22 04:05, Gianni Ceccarelli wrote: If you can use bash, or set up some redirections from whatever you're using to execute ``psql``, you can do:: $ psql somedb --set num=42 <<<'select :num' Timing is on. Expanded display is used automatically. Line style is unicode. Border style is 2. ┌──┐ │ ?column? │ ├──┤ │ 42 │ └──┘ (1 row) Time: 0.517 ms or (more classically):: echo 'select :num' | psql somedb --set num=42 Since my process uses the same variable in multiple bash statements, I decided to use a bash environment variable. -- Angular momentum makes the world go 'round.
Re: Strange collation names ("hu_HU.UTF-8")
Durumdara writes: > Today we found strange database collation names in a server (V11). > "hu_HU.UTF-8" > "hu_HU.UTF8" > "hu_HU.utf8" Yeah, these are all the same so far as the operating system is concerned. I believe most if not all variants of Unix are permissive about the spelling of the encoding part. > What I don't understand, that if I query for collations, I got only this: > hu_HU.utf8 pg_collation generally contains only "canonical" spellings of the locale names, because initdb builds it from what "locale -a" prints. However, different OS releases may have different ideas about which encoding name is canonical. > The whole problem appeared when we wanted to copy a database to a new (with > defining the old as template). > *Error: new collation (hu_HU.utf8) is incompatible with the collation of > the template database (hu_HU.UTF-8)* The code that checks that isn't as permissive as libc. You can spell it exactly the same, or if you wanted to live dangerously you could manually update the template database's pg_database entry to use the currently-canonical spelling. (I'd try that in a scratch installation first ...) There was some discussion not long ago about relaxing the check for "same collation name" [1], but no one has written a patch. regards, tom lane [1] https://www.postgresql.org/message-id/flat/fedc0205-c15b-e400-aa3f-e1d2a1285ddb%40sourcepole.ch
ICU is not supported in this build. install from source code.
Install from source code(follow the manual) system version: Ubuntu 22.04 LTS pg version: PostgreSQL 15beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit install step: > ./configure --with-perl --with-python --with-icu > ICU_CFLAGS='-I/usr/include/unicode' ICU_LIBS='-L/usr/lib/icu' > --enable-debug --with-pgport=5439 > make world > sudo su > make install world > mkdir -p /usr/local/pgsql/data > chown jian /usr/local/pgsql/data > su - jian > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data > /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start > /usr/local/pgsql/bin/createdb test > /usr/local/pgsql/bin/psql test > I installed ICU4C 71.1package. use /usr/bin/icuinfo return > > Copyright (C) 2016 and later: Unicode, Inc. > and others. License & terms of use: http://www.unicode.org/copyright.html > > icu4c > International Components for Unicode for > C/C++ > 71.1 > 14.0 > 4000 > Linux > en_US > en-US > UTF-8 > icudt71l > > 41.0 > 2022a > Asia/Kolkata > 64 > 0 > 4 > 0 > x86_64-pc-linux-gnu > x86_64-pc-linux-gnu > gcc > g++ > 1 > 1 > > > > ICU Initialization returned: U_ZERO_ERROR Plugins are disabled. > But now I cannot ICU. when I create an collation related to ICU then > ERROR: ICU is not supported in this build > -- I recommend David Deutsch's <> Jian
Copying records from TABLE_A to TABLE_B (in the same database)
AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? -- Angular momentum makes the world go 'round.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 12:37, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? create view ?
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 11:37 AM, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? Does table_a have existing records? If so do you care if there are duplicates? How large a data set are you talking about? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 13:41, Rob Sargent wrote: On 8/2/22 12:37, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? create view ? No, we need separate copies. -- Angular momentum makes the world go 'round.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? The INSERT is most probably faster then \copy Another option is to have a trigger on table_a to automatically replay all DML on table_b Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases)
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 13:41, Adrian Klaver wrote: On 8/2/22 11:37 AM, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; Argh, I got the tables backwards. Should be: INSERT INTO table_b SELECT * FROM table_a; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? Does table_a have existing records? Yes. Just before the copy, table_b was created using: CREATE TABLE table_b (LIKE table_a INCLUDING CONSTRAINTS INCLUDING DEFAULTS); The only constraints on table_a are NOT NULL on various fields. If so do you care if there are duplicates? TABLE_A (the source) has a UNIQUE index. I'll be adding a similar PK on TABLE_B after the copy. How large a data set are you talking about? It's varied. The biggest have up to 20M rows with a bytea field, and others with 50M rather large (but no bytea) fields. INSERT INTO is good enough for the small tables. -- Angular momentum makes the world go 'round.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 12:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? The INSERT is most probably faster then \copy Another option is to have a trigger on table_a to automatically replay all DML on table_b Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases) If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 13:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? The INSERT is most probably faster then \copy That's what I figured, since COPY means moving data over the (admittedly very fast) wire, onto disk, and then back over the wire into the new table. Another option is to have a trigger on table_a to automatically replay all DML on table_b How would you originally populate it? Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases) -- Angular momentum makes the world go 'round.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 8/2/22 13:59, Rob Sargent wrote: On 8/2/22 12:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY); \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY); Is there a faster/better way? The INSERT is most probably faster then \copy Another option is to have a trigger on table_a to automatically replay all DML on table_b Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases) If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me. ?? -- Angular momentum makes the world go 'round.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases) If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me. ?? I meant using tools other than sql (and psql). I have java code using org.postgresql.copy package because straight insert was too slow for large numbers of rows.
Re: Copying records from TABLE_A to TABLE_B (in the same database)
On 2022-08-02 13:08:41 -0600, Rob Sargent wrote: > If you can get outside sql, the bulk copy facilities (CopyManager in > java) is blindingly fast for me. > > > ?? > > > I meant using tools other than sql (and psql). I have java code using > org.postgresql.copy package I don't know that package but I'm pretty sure it's just using the PostgreSQL COPY command. > because straight insert was too slow for large numbers of rows. The OP already has the data in the database. In my experience[1] copying from one table to another is quite a bit faster that copying into the database (and therefore also copying out AND copying in). hp [1] https://github.com/hjp/dbbench/blob/master/import_pg_comparison/results/akran.2019-12-15/results.png -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Strange collation names ("hu_HU.UTF-8")
On Wed, Aug 3, 2022 at 1:43 AM Tom Lane wrote: > I believe most if not all variants of Unix are > permissive about the spelling of the encoding part. I've only seen glibc doing that downcase-and-strip-hyphens thing to the codeset part of a locale name when looking for locale definition files. Other systems like FreeBSD expect to be able to open /usr/share/locale/$LC_COLLATE/LC_COLLATE directly without any kind of munging. On a Mac it's probably a little fuzzy because the filenames are case insensitive...
