Thread: duplicate key violates unique constraint

duplicate key violates unique constraint

From
"Shavonne Marietta Wijesinghe"
Date:
Hello
 
During an "INSERT INTO" I get an "Error - duplicate key violates unique constraint...."
 
Is there any way, that i can test the error. Something like this??
 
IF error = "duplicate key violates unique constraint" then
    do something
else
    insert into
end if
 
 
Thanks
 
Shavonne

Re: duplicate key violates unique constraint

From
Teemu Torma
Date:
On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
> During an "INSERT INTO" I get an "Error - duplicate key violates
> unique constraint...."
>
> Is there any way, that i can test the error. Something like this??
>
> IF error = "duplicate key violates unique constraint" then
>     do something
> else
>     insert into
> end if

insert into ...;
exception when unique_violation then do something;

Teemu


Re: duplicate key violates unique constraint

From
"Shavonne Marietta Wijesinghe"
Date:
Thank you. I tried as you said. But i get a ERROR:  syntax error at or near 
"INSERT" at character 9

BEGININSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTIONWHEN unique_violation THENINSERT INTO my_shevi values ('a', 5, 2, 2);
END;

I don't see anything wrong with the code..

Shavonne

----- Original Message ----- 
From: "Teemu Torma" <teemu@torma.org>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint


> On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
>> During an "INSERT INTO" I get an "Error - duplicate key violates
>> unique constraint...."
>>
>> Is there any way, that i can test the error. Something like this??
>>
>> IF error = "duplicate key violates unique constraint" then
>> Â Â do something
>> else
>> Â Â insert into
>> end if
>
> insert into ...;
> exception when unique_violation then
>  do something;
>
> Teemu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings 



Re: duplicate key violates unique constraint

From
Andrew Sullivan
Date:
On Tue, Feb 26, 2008 at 05:05:01PM +0100, Shavonne Marietta Wijesinghe wrote:
> Thank you. I tried as you said. But i get a ERROR:  syntax error at or near 
> "INSERT" at character 9
> I don't see anything wrong with the code..

Well, except that there's no "EXCEPTION" statement in SQL?  I think your
correspondent was intending for this to be programmatic.  You can do what
you want with a subtransaction, though.  Look into savepoints in the manual.

A



Re: duplicate key violates unique constraint

From
Teemu Torma
Date:
On Tuesday 26 February 2008, Andrew Sullivan wrote:
> Well, except that there's no "EXCEPTION" statement in SQL?  I think
> your correspondent was intending for this to be programmatic.

I was thinking pl/pgsql.  

Teemu


Re: duplicate key violates unique constraint

From
"Bart Degryse"
Date:
Shavonne,
You will probably always find someone on the list who will answer your questions, but you really should read the manual too!
In this case you could have found the answer by reading
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2008-02-26 17:05 >>>
Thank you. I tried as you said. But i get a ERROR:  syntax error at or near
"INSERT" at character 9

BEGIN
INSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTION
WHEN unique_violation THEN
INSERT INTO my_shevi values ('a', 5, 2, 2);
END;

I don't see anything wrong with the code..

Shavonne

----- Original Message -----
From: "Teemu Torma" <teemu@torma.org>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint


> On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
>& gt; During an "INSERT INTO" I get an "Error - duplicate key violates
>> unique constraint...."
>>
>> Is there any way, that i can test the error. Something like this??
>>
>> IF error = "duplicate key violates unique constraint" then
>> Â Â do something
>> else
>> Â Â insert into
>> end if
>
> insert into ...;
> exception when unique_violation then
>  do something;
>
> Teemu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: duplicate key violates unique constraint

From
"Shavonne Marietta Wijesinghe"
Date:
I wrote the query in the SQL windows provided by the pgadmin.

According to the examples in "Chapter 35. PL/pgSQL - SQL Procedural 
Language" i don't see what i did different.
   BEGIN       SAVEPOINT s1;       ... code here ...   EXCEPTION       WHEN ... THEN           ROLLBACK TO s1;
... code here ...       WHEN ... THEN           ROLLBACK TO s1;           ... code here ...   END;
 

Maybe missing tabs before the statement??

Shavonne

----- Original Message ----- 
From: "Teemu Torma" <teemu@torma.org>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 26, 2008 5:28 PM
Subject: Re: [SQL] duplicate key violates unique constraint


> On Tuesday 26 February 2008, Andrew Sullivan wrote:
>> Well, except that there's no "EXCEPTION" statement in SQL? Â I think
>> your correspondent was intending for this to be programmatic.
>
> I was thinking pl/pgsql.
>
> Teemu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend 



Re: duplicate key violates unique constraint

From
Alvaro Herrera
Date:
Shavonne Marietta Wijesinghe wrote:
> I wrote the query in the SQL windows provided by the pgadmin.

That SQL window only executes SQL, so you can't use PL/pgSQL commands on
it.  PL/pgSQL can only be used in the context of a PL/pgSQL function, so
you need to do CREATE FUNCTION to do that.

If you want to use the SQL window you could use SAVEPOINT and ROLLBACK TO.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: duplicate key violates unique constraint

From
"Rodrigo E. De León Plicet"
Date:
On Tue, Feb 26, 2008 at 11:36 AM, Shavonne Marietta Wijesinghe
<shavonne.marietta@studioform.it> wrote:
>     BEGIN
>         SAVEPOINT s1;
>         ... code here ...
>     EXCEPTION
>         WHEN ... THEN
>             ROLLBACK TO s1;
>             ... code here ...
>         WHEN ... THEN
>             ROLLBACK TO s1;
>             ... code here ...
>     END;

PostgreSQL doesn't have anonymous blocks.

You need to write a function.


Re: duplicate key violates unique constraint

From
"Edward W. Rouse"
Date:
Could it be that the insert statement itself is the problem? What does the table look like?
 
 

Edward W. Rouse

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Bart Degryse
Sent: Tuesday, February 26, 2008 11:35 AM
To: pgsql-sql@postgresql.org; Shavonne Marietta Wijesinghe
Subject: Re: [SQL] duplicate key violates unique constraint

Shavonne,
You will probably always find someone on the list who will answer your questions, but you really should read the manual too!
In this case you could have found the answer by reading
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2008-02-26 17:05 >>>
Thank you. I tried as you said. But i get a ERROR:  syntax error at or near
"INSERT" at character 9

BEGIN
INSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTION
WHEN unique_violation THEN
INSERT INTO my_shevi values ('a', 5, 2, 2);
END;

I don't see anything wrong with the code..

Shavonne

----- Original Message -----
From: "Teemu Torma" <teemu@torma.org>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint


> On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
>& gt; During an "INSERT INTO" I get an "Error - duplicate key violates
>> unique constraint...."
>>
>> Is there any way, that i can test the error. Something like this??
>>
>> IF error = "duplicate key violates unique constraint" then
>> Â Â do something
>> else
>> Â Â insert into
>> end if
>
> insert into ...;
> exception when unique_violation then
>  do something;
>
> Teemu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings