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.1 (but this works from 9.5.x onward)
Manual PostgreSQL 12.1: 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 (Archbishop) on Jan 28, 2020 at 15:00 UTC | |
Re^2: [OT] SQL "on duplicate key" custom action
by haukex (Archbishop) on Oct 21, 2019 at 18:22 UTC | |
by erix (Prior) on Oct 22, 2019 at 09:45 UTC |
In Section
Seekers of Perl Wisdom