Обсуждение: 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

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 

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

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

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 

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

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.

-----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