Обсуждение: sequence last_value
Hi
I really need help on this one. can any one please explain to me how
"SERIAL PRIMARY KEY "
works.
I keep getting the following err "Cannot insert a duplicate key into unique
index loc_pkey"
I have deleted this table and rebuild it, renamed it, deleted it and made a
new table with a new name and still get the above err's
(the table was called location)
wireman=# insert into loc values (1,1,'HELP');
ERROR: Cannot insert a duplicate key into unique index loc_pkey
my table looks like this:
CREATE TABLE location (
building_key INT2,
floor INT2,
ref VARCHAR(16),
key_location SERIAL PRIMARY KEY
);
wireman=# select * from loc;
building_key | floor | ref | key_location
--------------+-------+------+--------------
1 | 3 | 36 | 1
1 | 3 | DA05 | 2
1 | 3 | CN05 | 3
1 | 3 | CB05 | 4
1 | 3 | DG30 | 5
1 | 3 | DF30 | 7
1 | 3 | DR29 | 8
1 | 3 | CO30 | 9
1 | 3 | CM30 | 10
1 | 3 | DF26 | 11
1 | 3 | DD26 | 12
1 | 3 | DF22 | 16
1 | 3 | CR22 | 18
1 | 3 | BV22 | 19
1 | 3 | BT22 | 21
1 | 3 | BV05 | 23
1 | 3 | CT05 | 24
1 | 3 | DC26 | 27
1 | 3 | DR26 | 30
1 | 3 | DM26 | 31
1 | 3 | DG18 | 32
1 | 3 | BV26 | 33
1 | 3 | DM21 | 35
1 | 3 | CO22 | 36
1 | 3 | BT17 | 37
1 | 3 | BU29 | 41
1 | 3 | BT14 | 43
1 | 3 | DR21 | 45
1 | 3 | BT29 | 46
1 | 3 | CP30 | 47
(30 rows)
or
wireman=# \d loc
Table "loc"
Attribute | Type | Modifier
--------------+-------------+-----------------------------------------------
---------
building_key | smallint |
floor | smallint |
ref | varchar(16) |
key_location | integer | not null default
nextval('loc_key_location_seq'::text)
Index: loc_pkey
I don't know if this matter but I got this,
wireman=# select * from loc_key_location_seq;
sequence_name | last_value | increment_by | max_value | min_value |
cache_value | is_cycled | is_called
----------------------+------------+--------------+------------+-----------+
-------------+-----------+-----------
loc_key_location_seq | 5 | 1 | 2147483647 | 1 |
1 | f | t
now my other question is, where do I look to fix this problem?
i'm running PostgreSQL 7.0.3 on sparc-sun-solaris2.8, compiled by gcc 2.95.2
also see my previous post
thanx in advance.
thanx to all for the replies
fixed the problem by manually (postgres would not allow me to get a script
(PHP) to do it) repeating an insert sql command until the last_value field
on location_key_location_seq was above 47. (the last index) haven't had any
more problems.
still a little worried about how this happened and thank goodness the table
was small and not the cable information table.
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Friday, October 12, 2001 8:50 PM
To: Duncan Adams (DNS); 'pgsql-novice@postgresql.org'
Subject: Re: [NOVICE] sequence last_value
>> wireman=# insert into loc values (1,1,'HELP');
>> ERROR: Cannot insert a duplicate key into unique index loc_pkey
>Try this instead:
>INSERT INTO loc ( building_key, floor, ref )
>VALUES (1, 1, 'Help');
>Explicit column naming in INSERTs is always a good idea.
was giving me the same error, found that if I added the index it worked,
provided that the index was not in use. but who wants to find the last index
every time you do an insert :{
>Also, upgrading to 7.1.3 would be a good idea, although unrelated to
>your problem.
spoken to sys admin and arranged downtime. Been trying to get him to do it
for a long time and thanx to your mail he is gona do it.
>Finally, SERIAL and NEXTVAL sequences do not work properly in older
>versions of pgODBC, if you're using a WIndows client. Upgrading to the
>new pgAdmin will solve this.
wilco.
>-Josh Berkus
>If you have dropped table, it would be necessary to drop
>key_location_seq explicitly.
you have to otherwise postgres will not allow you to recreate the table, it
looks as if postgres drop's the index (_pkey) table when the table is
dropped, is this correct? and when you say explicitly is this the same as
drop sequence?
>Vijay