Re: sequence last_value

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(dot)Burski(at)911ep(dot)com

800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com