Thread: Insert failure on serial

-- Microsoft Windows XP SP 2
-- Fails to insert new row into line_items with bogus error report -- seria=
l field 'id' not working.
-- This is the short form.  I'll also attach a file dumpdd.sql with the dum=
p output. Using verbosity verbose
-- didn't change the error message (see below).

depot_development=3D# \set
VERSION =3D 'PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (=
GCC) 3.4.2 (mingw-special)'
AUTOCOMMIT =3D 'on'
VERBOSITY =3D 'default'
PROMPT1 =3D '%/%R%x%# '
PROMPT2 =3D '%/%R%x%# ... '
PROMPT3 =3D '>> '
DBNAME =3D 'depot_development'
USER =3D 'depot'
PORT =3D '5432'
ENCODING =3D 'UTF8'



depot_development=3D# \dt line_items
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | line_items | table | depot
(1 row)

depot_development=3D# \d line_items;
                           Table "public.line_items"
   Column   |  Type   |                        Modifiers
------------+---------+----------------------------------------------------=
-----
 id         | integer | not null default nextval('line_items_id_seq'::regcl=
ass)
 product_id | integer | not null default 0
 order_id   | integer | not null default 0
 quantity   | integer | not null default 0
 unit_price | real    | not null default 0.0
Indexes:
    "line_items_pkey" PRIMARY KEY, btree (id)

depot_development=3D# select * from line_items;
 id | product_id | order_id | quantity | unit_price
----+------------+----------+----------+------------
  1 |          4 |        1 |        1 |    9999.99
  2 |          5 |        2 |        1 |       1.99
  3 |         10 |        2 |        1 |      88.98
  4 |          9 |        3 |        1 |      22.88
  5 |          8 |        4 |        1 |          1
  6 |         15 |        4 |        1 |      18.88
  7 |          4 |        4 |        1 |    9999.99
  8 |         11 |        5 |        3 |      17.99
  9 |         16 |        6 |        1 |      44.99
 10 |         15 |        6 |        1 |      18.88
 11 |         14 |        6 |        1 |         95
 12 |          2 |        6 |        1 |      79.99
 13 |         20 |        7 |        1 |     989.99
 14 |         12 |        7 |        1 |      79.99
 15 |         13 |        7 |        1 |      39.99
(15 rows)

depot_development=3D# INSERT INTO line_items ("order_id", "product_id", "qu=
antity", "unit_price") VALUES(1, 2, 2, 989.99);
ERROR:  duplicate key violates unique constraint "line_items_pkey"

Warren Seltzer wrote:
> PostgreSQL 8.1.3 on i686-pc-mingw32
> Windows XP SP2
>
> Summary of Insert failure (There IS NO DUPLICATE KEY):
> depot_development=# INSERT INTO line_items ("order_id", "product_id", "quantity",
> "unit_price") VALUES(1, 2, 2, 989.99);
> ERROR:  duplicate key violates unique constraint "line_items_pkey"

The problem is that the sequence attached to the "id" column is lower
than the values actually present in the column.  Try doing
SELECT setval(pg_get_serial_sequence('line_items', 'id'), 16);

and see if it fixes your problem.

Now, how did the sequence got out of sync?  Did you insert values to the
id column by hand?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.