Thread: duplicate key violates unique constraint
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')";
Can someone help me please?!!
Thanks,
Verônica
On Mon, 2006-09-18 at 19:47 -0300, vtaquette@globo.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?
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.
Regards,
Jeff Davis
-----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@globo.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-----
Ron Johnson wrote:
> On 09/18/06 19:25, Jeff Davis wrote:
>
>>On Mon, 2006-09-18 at 19:47 -0300, vtaquette@globo.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=# 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=# insert into projects (project_id, username )
> dupe_filenames-# values (1, 'foo');
> ERROR: duplicate key violates unique constraint "projects_pkey"
>
>
If you insert a project_id, yes. The original query from vtaquette does not.
brian
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 09/18/06 21:19, brian wrote:
> Ron Johnson wrote:
>> On 09/18/06 19:25, Jeff Davis wrote:
>>
>>> On Mon, 2006-09-18 at 19:47 -0300, vtaquette@globo.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=# 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=# insert into projects (project_id, username )
>> dupe_filenames-# values (1, 'foo');
>> ERROR: duplicate key violates unique constraint "projects_pkey"
>>
>>
>
> If you insert a project_id, yes. The original query from vtaquette does
> not.
Ah, darn it. Gotta get those glasses.
Still, though...
# select * from projects;
project_id | username | project_name
- ------------+----------+--------------
1 | foo |
2 | bar |
(2 rows)
dupe_filenames=# insert into projects (project_name, username )
dupe_filenames-# values ('foo', 'bar');
ERROR: duplicate key violates unique constraint "projects_pkey"
- --
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
iD8DBQFFD1c5S9HxQb37XmcRAl5zAKC3wxSPwaFhGO58stMhryhrsKgFdwCg5729
gZTe/3iYcJO7aLY2IyixoUw=
=riVP
-----END PGP SIGNATURE-----
Ron Johnson wrote:
> # select * from projects;
> project_id | username | project_name
> - ------------+----------+--------------
> 1 | foo |
> 2 | bar |
> (2 rows)
>
> dupe_filenames=# insert into projects (project_name, username )
> dupe_filenames-# values ('foo', 'bar');
> ERROR: duplicate key violates unique constraint "projects_pkey"
And you didn't insert records with those numbers by hand (not using the
sequence)? It seems your sequence is a bit behind, which only happens if
you don't always use it to generate your ids.
I suggest you check your sequence values and update it to the highest
value in use if it's too low. You should only need to do that once.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
Hey, I've just find out what's happening. The problem is the "serial" datatype creates a sequence in the background (project_id_seq). If the sequence current numeber is 1, and I manually insert a new entry whit ID=2, the sequence doesn't "know" it. So when I try the INSERT statement, the next value in sequence is 2, and I get the error. The thing is, I'm migrating my system from mysql to postgresql, and that's why I was inserting directely the numbers (importing the .sql file), without respecting the backgroud sequence. Thanks a lot for those who answered me. Regards, Verônica