Re: sequence last_value - Mailing list pgsql-novice

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