Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

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

by 1nickt (Abbot)
on Apr 14, 2020 at 03:19 UTC ( #11115500=note: print w/replies, xml ) Need Help??


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;
... 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
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.

        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) ;

        See: CREATE INDEX in the fine manual

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11115500]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2020-10-26 16:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (252 votes). Check out past polls.

    Notices?