Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: [OT] SQL "on duplicate key" custom action

by erix (Parson)
on Oct 21, 2019 at 09:43 UTC ( #11107753=note: print w/replies, xml ) Need Help??


in reply to [OT] SQL "on duplicate key" custom action

if the primary key exists, it should silently ignore the record if all the other columns match, but throw an error if they don't.

I'd think a unique index including any extra columns would make this version of your INSERT work:

drop table if exists testtable; create table testtable (pk int primary key, foo text, bar te +xt); create unique index foobar_idx on testtable (pk, foo, bar); INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'World') ON C +ONFLICT (pk,foo,bar) DO NOTHING; INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'World') ON C +ONFLICT (pk,foo,bar) DO NOTHING; INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'Quz' ) ON C +ONFLICT (pk,foo,bar) DO NOTHING;

which yields:

drop table if exists testtable; DROP TABLE create table testtable (pk int primary key, foo text, bar te +xt); CREATE TABLE create unique index foobar_idx on testtable (pk, foo, bar); CREATE INDEX INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'World') ON C +ONFLICT (pk,foo,bar) DO NOTHING; INSERT 0 1 INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'World') ON C +ONFLICT (pk,foo,bar) DO NOTHING; INSERT 0 0 INSERT INTO testtable (pk, foo, bar) VALUES (1, 'Hello', 'Quz' ) ON C +ONFLICT (pk,foo,bar) DO NOTHING; ERROR: duplicate key value violates unique constraint "testtable_pkey +" DETAIL: Key (pk)=(1) already exists.

Postgres 12.0 (but this works from 9.5.x onward)

Manual PostgreSQL 12.0: INSERT # SQL-ON-CONFLICT

update: The first INSERT should be the same, of course, fixed. Also, removed vestiges of the original bash-version.

update 2: Re 'MySQL vs. Postgres': I think open-sourced and completely-free PostgreSQL is a better/safer choice than proprietary Oracle MySQL. For one thing, over time Pg will get better -- even better than Oracle -- but how could Oracle ever allow cheapo MySQL to overtake its venerable and expensive Oracle database product?

Replies are listed 'Best First'.
Re^2: [OT] SQL "on duplicate key" custom action
by haukex (Chancellor) on Oct 21, 2019 at 18:22 UTC

    Thank you for this, I figured there was probably a fairly easy way to do it that I was missing! I'll try and compare it with Corion's trigger suggestion in terms of disk space and speed.

      I tried to get that trigger-version working but I could not (well, not in a satisfactory way). I'm beginning to think it's not possible -- so if you hit upon a trigger-solution, I'd very much like to know. Thanks!

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2019-11-18 19:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (92 votes). Check out past polls.

    Notices?