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.
