pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: duplicate key violates unique constraint
17+ messages / 10 participants
[nested] [flat]
* duplicate key violates unique constraint
@ 2005-06-13 16:22 ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
2005-06-13 14:45 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 2 replies; 17+ messages in thread
From: ON.KG @ 2005-06-13 16:22 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi All!
I have table:
CREATE TABLE table1 (
ip char(15) NOT NULL,
hits integer NOT NULL default '1',
PRIMARY KEY (ip)
);
So it's counting hits per each IP for current day and every day
trancated by cron:
TRUNCATE TABLE table1;
before inserting or updating this table there're some checkings,
logs, etc., so I'm using PL/PgSQL for that
after all checkings and logs I have:
UPDATE table1
SET hits = hits + 1
WHERE ip = some_ip;
IF NOT FOUND THEN
INSERT INTO table1
(ip)
VALUES
(some_ip);
END IF;
when IP is not found in table it inserts new record into table
but in logs i see error
ERROR: duplicate key violates unique constraint "table1"
CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement
But record is inserted into table
what may be the problem?
i also tried before:
SELECT INTO cnt hits
FROM table1
WHERE ip = some_ip;
IF FOUND THEN
UPDATE table1
SET hits = hits + 1
WHERE ip = some_ip;
ELSE
INSERT INTO table1
(ip)
VALUES
(some_ip);
END IF;
But same error still appears
Thank You
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
@ 2005-06-13 14:43 ` Richard Huxton <dev@archonet.com>
2005-06-13 15:22 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
1 sibling, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2005-06-13 14:43 UTC (permalink / raw)
To: ON.KG <skyer@on.kg>; +Cc: pgsql-general@postgresql.org
ON.KG wrote:
>
> before inserting or updating this table there're some checkings,
> logs, etc., so I'm using PL/PgSQL for that
>
> after all checkings and logs I have:
>
> UPDATE table1
> SET hits = hits + 1
> WHERE ip = some_ip;
>
> IF NOT FOUND THEN
> INSERT INTO table1
> (ip)
> VALUES
> (some_ip);
> END IF;
>
> when IP is not found in table it inserts new record into table
> but in logs i see error
> ERROR: duplicate key violates unique constraint "table1"
> CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement
If you can have more than one client running this at once you have a
race condition here. The order runs something like:
1. client A tries to update SOME_IP, no rows affected
2. client B tries to update SOME_IP, no rows affected
3. client A tries the insert of SOME_IP
4. client B tries the insert of SOME_IP - fails!
If you have more than one client, this can always happen. You have two
choices:
1. Use a lock to stop two clients interacting like this
2. Catch the error on the insert and try the update again. This
requires version 8.0 or higher.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
@ 2005-06-13 15:22 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 15:49 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Csaba Nagy @ 2005-06-13 15:22 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: ON.KG <skyer@on.kg>; Postgres general mailing list <pgsql-general@postgresql.org>
[snip]
> If you have more than one client, this can always happen. You have two
> choices:
> 1. Use a lock to stop two clients interacting like this
This won't work unless you make all the clients serialized, or you have
all the ip's already inserted in the data base... you can't lock on an
unknown key, otherwise the locking will also need to insert, and you're
back to the same race condition ;-)
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
2005-06-13 15:22 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
@ 2005-06-13 15:49 ` Richard Huxton <dev@archonet.com>
2005-06-13 15:58 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 1 reply; 17+ messages in thread
From: Richard Huxton @ 2005-06-13 15:49 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: ON.KG <skyer@on.kg>; Postgres general mailing list <pgsql-general@postgresql.org>
Csaba Nagy wrote:
> [snip]
>
>>If you have more than one client, this can always happen. You have two
>>choices:
>> 1. Use a lock to stop two clients interacting like this
>
>
> This won't work unless you make all the clients serialized, or you have
> all the ip's already inserted in the data base... you can't lock on an
> unknown key, otherwise the locking will also need to insert, and you're
> back to the same race condition ;-)
You can, however, have something more finely-grained than whole-table
locking (assuming one IP updated/inserted at a time) by filling a dummy
table with e.g. integers 0..255 and locking a row there based on (e.g.)
the last octet of your target IP.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
2005-06-13 15:22 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 15:49 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
@ 2005-06-13 15:58 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 16:03 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Csaba Nagy @ 2005-06-13 15:58 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: ON.KG <skyer@on.kg>; Postgres general mailing list <pgsql-general@postgresql.org>
That would work indeed. Bit I guess the savepoint solution will be the
simplest and fastest if the OP has or can install 8.0 version.
Cheers,
Csaba.
On Mon, 2005-06-13 at 17:49, Richard Huxton wrote:
> Csaba Nagy wrote:
> > [snip]
> >
> >>If you have more than one client, this can always happen. You have two
> >>choices:
> >> 1. Use a lock to stop two clients interacting like this
> >
> >
> > This won't work unless you make all the clients serialized, or you have
> > all the ip's already inserted in the data base... you can't lock on an
> > unknown key, otherwise the locking will also need to insert, and you're
> > back to the same race condition ;-)
>
> You can, however, have something more finely-grained than whole-table
> locking (assuming one IP updated/inserted at a time) by filling a dummy
> table with e.g. integers 0..255 and locking a row there based on (e.g.)
> the last octet of your target IP.
>
> --
> Richard Huxton
> Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
2005-06-13 15:22 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 15:49 ` Re: duplicate key violates unique constraint Richard Huxton <dev@archonet.com>
2005-06-13 15:58 ` Re: duplicate key violates unique constraint Csaba Nagy <nagy@ecircle-ag.com>
@ 2005-06-13 16:03 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 17+ messages in thread
From: Richard Huxton @ 2005-06-13 16:03 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: ON.KG <skyer@on.kg>; Postgres general mailing list <pgsql-general@postgresql.org>
Csaba Nagy wrote:
> That would work indeed. Bit I guess the savepoint solution will be the
> simplest and fastest if the OP has or can install 8.0 version.
I'd say so. Otherwise you'll just sit on the lock, and then still have
to deal with an error later anyway when the lock times out.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
@ 2005-06-13 14:45 ` Csaba Nagy <nagy@ecircle-ag.com>
1 sibling, 0 replies; 17+ messages in thread
From: Csaba Nagy @ 2005-06-13 14:45 UTC (permalink / raw)
To: ON.KG <skyer@on.kg>; +Cc: Postgres general mailing list <pgsql-general@postgresql.org>
Your problem is that the trigger's "found" check will not see the row
inserted by a concurrent transaction. In other words, your insert
actually fails, the record what you see was inserted by another
concurrent transaction, and the "found" check didn't work because the
other transaction started after yours, but was quicker, and your
transaction can't see it's results.
What you try to do is similar to the "insert-or-update" thing, which
cannot be done safely in the way you tried to do it. Don't even bother
to try, there are lots of discussions on the list and the conclusion is
you can't avoid a race condition between the concurrent inserts. There
always will be a way one of them will fail with an error.
You could actually ignore the error if it's not part of a bigger
transaction, which would of course be broken by the error.
Your only way to avoid the error completely is to place a save point
before the insert, catch the error, and roll back to the save point, and
then continue your transaction as you need.
HTH,
Csaba.
On Mon, 2005-06-13 at 18:22, ON.KG wrote:
> Hi All!
>
> I have table:
>
> CREATE TABLE table1 (
> ip char(15) NOT NULL,
> hits integer NOT NULL default '1',
> PRIMARY KEY (ip)
> );
>
> So it's counting hits per each IP for current day and every day
> trancated by cron:
> TRUNCATE TABLE table1;
>
> before inserting or updating this table there're some checkings,
> logs, etc., so I'm using PL/PgSQL for that
>
> after all checkings and logs I have:
>
> UPDATE table1
> SET hits = hits + 1
> WHERE ip = some_ip;
>
> IF NOT FOUND THEN
> INSERT INTO table1
> (ip)
> VALUES
> (some_ip);
> END IF;
>
> when IP is not found in table it inserts new record into table
> but in logs i see error
> ERROR: duplicate key violates unique constraint "table1"
> CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement
>
> But record is inserted into table
>
> what may be the problem?
>
> i also tried before:
> SELECT INTO cnt hits
> FROM table1
> WHERE ip = some_ip;
>
> IF FOUND THEN
> UPDATE table1
> SET hits = hits + 1
> WHERE ip = some_ip;
> ELSE
> INSERT INTO table1
> (ip)
> VALUES
> (some_ip);
> END IF;
>
> But same error still appears
>
> Thank You
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 17+ messages in thread
* duplicate key violates unique constraint
@ 2008-02-26 14:55 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
0 siblings, 1 reply; 17+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-26 14:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-26 15:17 ` Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 17+ messages in thread
From: Teemu Torma @ 2008-02-26 15:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
@ 2008-02-26 16:05 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Re: duplicate key violates unique constraint Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:34 ` Re: duplicate key violates unique constraint Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 17+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-26 16:05 UTC (permalink / raw)
To: Teemu Torma <teemu@torma.org>; pgsql-sql
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:
>> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-26 16:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:28 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
1 sibling, 1 reply; 17+ messages in thread
From: Andrew Sullivan @ 2008-02-26 16:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Re: duplicate key violates unique constraint Andrew Sullivan <ajs@crankycanuck.ca>
@ 2008-02-26 16:28 ` Teemu Torma <teemu@torma.org>
2008-02-26 16:36 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 17+ messages in thread
From: Teemu Torma @ 2008-02-26 16:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Re: duplicate key violates unique constraint Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:28 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
@ 2008-02-26 16:36 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 17:03 ` Re: duplicate key violates unique constraint Alvaro Herrera <alvherre@commandprompt.com>
2008-02-26 17:09 ` Re: duplicate key violates unique constraint Rodrigo E. De León Plicet <rdeleonp@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-26 16:36 UTC (permalink / raw)
To: Teemu Torma <teemu@torma.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Re: duplicate key violates unique constraint Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:28 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:36 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-26 17:03 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2008-02-26 17:03 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Teemu Torma <teemu@torma.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Re: duplicate key violates unique constraint Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:28 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:36 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-26 17:09 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Rodrigo E. De León Plicet @ 2008-02-26 17:09 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Teemu Torma <teemu@torma.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-26 16:34 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 19:29 ` Re: duplicate key violates unique constraint Edward W. Rouse <erouse@comsquared.com>
1 sibling, 1 reply; 17+ messages in thread
From: Bart Degryse @ 2008-02-26 16:34 UTC (permalink / raw)
To: pgsql-sql; Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
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:
>> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: duplicate key violates unique constraint
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Re: duplicate key violates unique constraint Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Re: duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:34 ` Re: duplicate key violates unique constraint Bart Degryse <Bart.Degryse@indicator.be>
@ 2008-02-26 19:29 ` Edward W. Rouse <erouse@comsquared.com>
0 siblings, 0 replies; 17+ messages in thread
From: Edward W. Rouse @ 2008-02-26 19:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2008-02-26 19:29 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-13 16:22 duplicate key violates unique constraint ON.KG <skyer@on.kg>
2005-06-13 14:43 ` Richard Huxton <dev@archonet.com>
2005-06-13 15:22 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 15:49 ` Richard Huxton <dev@archonet.com>
2005-06-13 15:58 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-06-13 16:03 ` Richard Huxton <dev@archonet.com>
2005-06-13 14:45 ` Csaba Nagy <nagy@ecircle-ag.com>
2008-02-26 14:55 duplicate key violates unique constraint Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 15:17 ` Teemu Torma <teemu@torma.org>
2008-02-26 16:05 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 16:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2008-02-26 16:28 ` Teemu Torma <teemu@torma.org>
2008-02-26 16:36 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-26 17:03 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-02-26 17:09 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-02-26 16:34 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-02-26 19:29 ` Edward W. Rouse <erouse@comsquared.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox