Re: duplicate key violates unique constraint

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/18/06 19:25, Jeff Davis wrote:
> On Mon, 2006-09-18 at 19:47 -0300, vtaquette(at)globo(dot)com wrote:
>> Hi,
>>
>> I'm trying to create a table with a PRIMARY KEY. The CREATE statement looks
>> like this:
>>
>> CREATE TABLE "projects" (
>> "project_id" serial,
>> "username" varchar(30) NOT NULL default '',
>> "project_name" varchar(30) NOT NULL default '',
>> PRIMARY KEY ("project_id")
>> ) ;
>>
>> The problem is that sometimes, I would say 1 in 10 tries, when I use a INSERT
>> command I get the following error:
>>
>> "duplicate key violates unique constraint"
>>
>> The INSERT query is that:
>> "INSERT INTO projects (\"project_name\", \"username\") VALUES ('$project_name',
>> '$username')";
>>
>
> That INSERT statement will not cause a unique constraint violation. Are
> you sure that is the statement causing the problem? Are there any rules
> or triggers that may modify the behavior of that INSERT?

If there already are records in the table, sure it would.

dupe_filenames=# create table projects (
dupe_filenames(# project_id serial,
dupe_filenames(# username varchar(30) NOT NULL default '',
dupe_filenames(# project_name varchar(30) NOT NULL default '',
dupe_filenames(# primary key (project_id));
NOTICE: CREATE TABLE will create implicit sequence
"projects_project_id_seq" for serial column "projects.project_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"projects_pkey" for table "projects"
CREATE TABLE

dupe_filenames=# insert into projects (project_id, username )
dupe_filenames-# values (1, 'foo');
INSERT 0 1
dupe_filenames=# insert into projects (project_id, username )
dupe_filenames-# values (2, 'bar');
INSERT 0 1
dupe_filenames=# select * from projects;
project_id | username | project_name
- ------------+----------+--------------
1 | foo |
2 | bar |
(2 rows)

dupe_filenames=# commit;
COMMIT

dupe_filenames=# insert into projects (project_id, username )
dupe_filenames-# values (1, 'foo');
ERROR: duplicate key violates unique constraint "projects_pkey"

> I suggest you turn on query logging, which you can do by setting the
> configuration variable "log_statement" (found in postgresql.conf) to
> 'all'. Then you can see exactly what queries are being sent and which
> one causes the error.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFD0xkS9HxQb37XmcRAhMfAKDsP9ZILY1IaBndVLU3r7OBHYFzLACeNADP
USrU5EV9ma6Lp0HWXGbieVw=
=aBmc
-----END PGP SIGNATURE-----