Date: Mon, 13 Jun 2005 20:22:39 +0400
From: "ON.KG" <skyer@on.kg>
Reply-To: "ON.KG" <skyer@on.kg>
Organization: UnCLouds
Message-ID: <10813889593.20050613202239@on.kg>
To: pgsql-general@postgresql.org
Subject: duplicate key violates unique constraint
In-Reply-To: <20050610095655.0215752956@svr1.postgresql.org>
References: <42A95936.3060002@archonet.com>
	<20050610095655.0215752956@svr1.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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