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

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