Обсуждение: 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.
Duncan, > > 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. Also, upgrading to 7.1.3 would be a good idea, although unrelated to your problem. 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. -Josh Berkus
The syntax for insertion could be like insert into loc (bulding_key,floor,ref) values (1,1 'help'); If you have dropped table, it would be necessary to drop key_location_seq explicitly. Vijay "Duncan Adams (DNS)" wrote: > > Hi > > I really need help on this one. can any one please explain to me how > "SERIAL PRIMARY KEY " > > wireman=# insert into loc values (1,1,'HELP'); > ERROR: Cannot insert a duplicate key into unique index loc_pkey
I think I can provide a bit of insight into this, but I'm pretty much a novice
myself.
PRIMARY KEY is just that, the primary key - each entry in the table must have a
unique entry in a column that is designated as a primary key. Additionally, an
index is dynamically created when the table is created, the "_pkey" index.
SERIAL is a PostgreSQL data type similar to a 4-byte integer type. It is
associated, as you know, with a sequence table, the "_seq" table.
When you drop a table, you should, IMO, drop the related sequences and indices
as well (they don't automatically drop).
Hope this helps.
"Duncan Adams (DNS)" wrote:
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN 56303
John.Burski@911ep.com
800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com
John Burski <johnb@911ep.com> writes:
> When you drop a table, you should, IMO, drop the related sequences and indices
> as well (they don't automatically drop).
Correction: indexes *are* dropped automatically when you drop their
table.
The sequence made to support a SERIAL column should be dropped
automatically when the table is dropped, but is not at present.
This will probably be fixed in some future release.
(I've heard some people suggest that they like the existing behavior,
but I think if you want a persistent sequence you should make it
with an explicit CREATE SEQUENCE command.)
This being the novice list, it should perhaps be pointed out that
SERIAL isn't a real datatype; it's just syntactic sugar for an
integer column with a default value expression, like so:
create table foo (bar serial);
is equivalent to
create sequence foo_bar_seq;
create table foo (bar integer unique not null
default nextval('foo_bar_seq'));
If you write it out longhand like this, you can obtain effects
like having several different tables draw serial numbers from
the same sequence, which comes in handy sometimes. If you've
set up an arrangement like that, then indeed you don't want the
sequence to be dropped just because you dropped one of the tables.
But ISTM that you should create the sequence manually when you
are going to do this. SERIAL is supposed to be a canned way of
setting up the simplest case, and in the simplest case I think
auto-drop is what you'd want...
regards, tom lane
I stand corrected. Thanks for the info. :)
Tom Lane wrote:
> John Burski <johnb@911ep.com> writes:
> > When you drop a table, you should, IMO, drop the related sequences and indices
> > as well (they don't automatically drop).
>
> Correction: indexes *are* dropped automatically when you drop their
> table.
>
> The sequence made to support a SERIAL column should be dropped
> automatically when the table is dropped, but is not at present.
> This will probably be fixed in some future release.
> (I've heard some people suggest that they like the existing behavior,
> but I think if you want a persistent sequence you should make it
> with an explicit CREATE SEQUENCE command.)
>
> This being the novice list, it should perhaps be pointed out that
> SERIAL isn't a real datatype; it's just syntactic sugar for an
> integer column with a default value expression, like so:
>
> create table foo (bar serial);
>
> is equivalent to
>
> create sequence foo_bar_seq;
>
> create table foo (bar integer unique not null
> default nextval('foo_bar_seq'));
>
> If you write it out longhand like this, you can obtain effects
> like having several different tables draw serial numbers from
> the same sequence, which comes in handy sometimes. If you've
> set up an arrangement like that, then indeed you don't want the
> sequence to be dropped just because you dropped one of the tables.
> But ISTM that you should create the sequence manually when you
> are going to do this. SERIAL is supposed to be a canned way of
> setting up the simplest case, and in the simplest case I think
> auto-drop is what you'd want...
>
> regards, tom lane
--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN 56303
John.Burski@911ep.com
800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com