Problem with unique key

From: "Greg Peters" <gregpeters79(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with unique key
Date: 2006-11-28 09:13:03
Message-ID: 9bc001470611280113k534e0e2nb44a89d444c206fb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

*Hello all, *
**
*I have a small problem I hope somebody can help with.*
**
*I have a table with the following definition:*

CSG=# \d admin_field_list
Table "public.admin_field_list"
Column | Type |
Modifiers
-------------+-----------------------------+----------------------------------------------------------------
key | bigint | not null default
nextval('admin_field_list_key_seq'::regclass)
field | character varying(25) | not null
added_by | character varying(25) | not null
add_date | timestamp without time zone | not null default now()
mod_date | timestamp without time zone |
modified_by | character varying(25) |
Indexes:
"admin_field_list_pkey" PRIMARY KEY, btree ("key")
"admin_field_list_field_key" UNIQUE, btree (field)

CSG=#

*As you can see, I have a primary key called "key", an autoincrementing
integer as per the usual. I also have a unique constraint set for the
"field" column because I don't want there to be duplicate entries in this
column.*

*This is the data currently in the table:*

**
CSG=# select * from admin_field_list;
key | field | added_by | add_date | mod_date |
modified_by
-----+------------------+-------------+-------------------------+----------+-------------
1 | Talinga | Greg Peters | 2006-08-28 22:14:53.593 | |
2 | Spring Gully | Greg Peters | 2006-08-28 22:14:53.609 | |
3 | Fairview | Greg Peters | 2006-08-28 22:14:53.609 | |
4 | Argyle | Greg Peters | 2006-08-28 22:14:53.609 | |
5 | Berwyndale South | Greg Peters | 2006-08-28 22:14:53.609 | |
6 | Peat | Greg Peters | 2006-08-28 22:14:53.625 | |
7 | Not Applicable | Greg Peters | 2006-08-28 22:14:53.625 | |
8 | Unknown | Greg Peters | 2006-08-28 22:14:53.625 | |
(8 rows)

CSG=#

*Now, as you can see, there is no field called "Kogan North", but when I
enter the command:*
**

CSG=# insert into admin_field_list (field, added_by) values ('Kogan North',
'Greg Peters');

*I get:*

ERROR: duplicate key violates unique constraint "admin_field_list_pkey"
CSG=#

*I don't understand what is going on. It seems that it can't increment the
primary key properly, or for some reason it's trying to assign an incorrect
value to the key column. If i change the command to include a value for the
key column (in this case the number 9), it seems to work. I've tried
dumping/restoring the DB and also tried it on v8.1 and v8.2 with no
success. Any ideas?*

Kind Regards,

Greg Peters.

* *

* *

* *

* *

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2006-11-28 09:29:45 Re: Problem with unique key
Previous Message Matthew Peter 2006-11-28 09:12:09 sequence help