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