duplicate key violates unique constraint

From: "ON(dot)KG" <skyer(at)on(dot)kg>
To: pgsql-general(at)postgresql(dot)org
Subject: duplicate key violates unique constraint
Date: 2005-06-13 16:22:39
Message-ID: 10813889593.20050613202239@on.kg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bob Pawley 2005-06-13 16:32:17 New to Postgre
Previous Message marcelo Cortez 2005-06-13 16:15:51 md5 autentication error

Browse pgsql-sql by date

  From Date Subject
Next Message postgres 2005-06-13 21:52:51 Re: Aggregate Functions Template
Previous Message Richard Huxton 2005-06-13 16:03:33 Re: duplicate key violates unique constraint