Re^3: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager

in reply to Re^2: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager
in thread Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager

Hi again, maybe what you need is an no-op "upsert", implemented in Postgres with INSERT ON CONFLICT ?

Something like

INSERT INTO myTable (foo, bar)
VALUES ('baz', 'qux') 
ON CONFLICT (foo) 
DO NOTHING;
[download]

... where foo is your column that has a unique key constraint.

Hope this helps!

The way forward always starts with a minimal test.

Comment on Re^3: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager Select or Download Code

Replies are listed 'Best First'.
Re^4: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager by perlygapes (Sexton) on Apr 30, 2020 at 13:53 UTC
Thank you for your suggestion. I realise I probably did not explain fully enough my goal. Let's say I have 10 columns in my table (excluding the PK). I only want to store a data row into the table if the combination of all 10 values is a unique combination set. Example with a 3 column table: `INSERT Row 1: A,A,A <== OK! Value set unique INSERT Row 2: A,D,A <== OK! Value set unique INSERT Row 3: D,T,G <== OK! Value set unique INSERT Row 4: D,A,A <== OK! Value set unique INSERT Row 5: A,D,A <== COLLISION with Row 2! Set not unique; skip IN +SERT` [download]	[reply] [d/l]
Re^4: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager by perlygapes (Sexton) on May 08, 2020 at 05:53 UTC
Thanks. I think I understand what that does, but the unique requirement is not on just one field, it is on the whole record. That is, the collection of all field values comprises a UNIQUE combination, and there must not be any other rows with that exact same combination of values.	[reply]
Re^5: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager by 1nickt (Canon) on May 08, 2020 at 16:33 UTC
Hi again, Add to your DB schema: `unique key all_cols_uk (every, column, name, in, the, table),` [download] https://www.w3schools.com/sql/sql_unique.asp Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l]
Re^6: Create parallel database handles or SQL statements for multi-threaded/process access to Postgres DB using DBI, DBD::Pg and Parallel::ForkManager by erix (Prior) on May 08, 2020 at 18:52 UTC
On that page postgres is not mentioned; in postgresql-SQL, that'd become, I think: `create unique index all_cols_uk on tablename (every, column, name, in, + the, table) ;` [download] See: CREATE INDEX in the fine manual	[reply] [d/l]

In Section Seekers of Perl Wisdom