From: | juleni <juleni(at)seznam(dot)cz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PROBLEM with inserting a duplicate key into unique index ... |
Date: | 2002-10-30 16:27:38 |
Message-ID: | 42150652677.20021030172738@seznam.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have a following (written below) plsql function which adds records
into the table "contact_place" and returns current inserted record ID
as a result (this is provided by function "getActualID(<seq_name>)").
Problem is, that sometimes is during inserting following error
occured:
java.sql.SQLException:
Cannot insert a duplicate key into unique index contact_place_id_contact_key
Primary key is of type SERIAL and the sequence shoul be set
automatically during inserting. After that I only find out this
current sequence (this is provided by function getActualID(...)).
Mostly are inserted records written currently, but sometimes is
error occured. I really don't know, what I'm doing wrong.
Can anybody help me with my problem?
Thanks in advance,
Julian.
Mailto : legeny(at)softhome(dot)net
===============================================================================
Create table contact_place (
id_contact Serial NOT NULL UNIQUE ,
fkid_contact_type Integer NOT NULL,
fkid_location Integer NOT NULL,
fkid_company Integer NOT NULL,
street Varchar(50) NOT NULL,
city Varchar(30) NOT NULL,
zip Numeric(6,0) NOT NULL,
primary key (id_contact,fkid_contact_type,fkid_location,fkid_company)
);
===============================================================================
CREATE OR REPLACE FUNCTION add_Contact_Place(INTEGER, INTEGER,INTEGER,VARCHAR(50)
,VARCHAR(30),NUMERIC(6,0))
RETURNS INTEGER AS '
DECLARE
pContactType ALIAS FOR $1;
pLocation ALIAS FOR $2;
pCompanyID ALIAS FOR $3;
pStreet ALIAS FOR $4;
pCity ALIAS FOR $5;
pZIP ALIAS FOR $6;
BEGIN
LOCK TABLE contact_place IN EXCLUSIVE MODE;
INSERT INTO contact_place (fkid_contact_type, fkid_location, fkid_company, street, city, zip)
VALUES (pContactType, pLocation, pCompanyID, pStreet, pCity, pZIP);
RETURN getActualID(''contact_place_id_contact_seq'');
END
' LANGUAGE 'plpgsql';
===============================================================================
-- Function which return current ID value from the specified sequence
CREATE OR REPLACE FUNCTION getActualID(text) RETURNS BIGINT AS '
SELECT CURRVAL($1);
' LANGUAGE 'sql';
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2002-10-30 16:28:29 | Re: maximum number of triggers on a table? |
Previous Message | Stephan Szabo | 2002-10-30 16:08:12 | Re: maximum number of triggers on a table? |