Thread: How to insert record only if primary key does not exist

Table is defined as

 

CREATE TABLE firma1.klient (

  kood character(12) NOT NULL DEFAULT nextval('firma1.klient_kood_seq'::regclass),

....

);

 

How to insert record to this table only if primary key does not exist ?

You want an operation that's called an UPSERT or MERGE operation. PostgreSQL doesn't have any native support to do this for you. Doing it right is surprisingly tricky. This is the best article I've seen on the topic:

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

--
Craig Ringer

On Mon, Jul 2, 2012 at 12:19 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> How to insert record to this table only if primary key does not exist ?
>
>
> You want an operation that's called an UPSERT or MERGE operation. PostgreSQL
> doesn't have any native support to do this for you. Doing it right is
> surprisingly tricky. This is the best article I've seen on the topic:
>
> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

Though that's aiming for a perfectly universal solution. There are
simpler solutions that work in restricted circumstances; the easiest
may be simply:

SAVEPOINT tryinsert
INSERT .... -- as normal
-- if error:
ROLLBACK TO SAVEPOINT tryinsert

Question: Is it better to simply do the insert as-is, or to have a
WHERE clause that will often, though not always, prevent duplicate
insertions?

ChrisA

Table is defined as

 

CREATE TABLE firma1.klient (

  kood character(12) NOT NULL DEFAULT nextval('firma1.klient_kood_seq'::regclass),

....

);

 

How to insert record to this table only if primary key does not exist ?

I tried code below but got strange error as shown in log file.

Why this error occurs ?

 

Andrus.

 

2012-06-30 15:51:04 EEST ERROR:  duplicate key value violates unique constraint "klient_pkey"

2012-06-30 15:51:04 EEST DETAIL:  Key (kood)=(20037       ) already exists.

2012-06-30 15:51:04 EEST STATEMENT:  insert into klient (

    kood,

    nimi,

    tanav,

    piirkond,

    postiindek

    )

    select ((E'20037')), ((E'Statoil Fuel & Retail')), ((E'')), ((E'10148 nnn')),((E''))

    from klient

    where not exists (select 1 from klient where kood =((E'20037')))

Pardon for my intrusion, but my experience has demonstrated that the decision if an insert or update is necessary should be done at application level.
Users will get mad with application "magically" changing their data in a multiuser high concurrence environment.
Now, if your case is just the insert, then you can try something similar to

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

documentation page at http://www.postgresql.org/docs/9.1/static/sql-insert.html


Just my 2c.

Regards,

Edson Richter