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

[Edson Richter <edsonrichter@xxxxxxxxxxx>]

Em 30/06/2012 10:02, Andrus escreveu:

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