Puzzling "duplicate key" error (PostgreSQL)

Question

First of all, let me say I understand relational theory and I'm as competent as anyone in MySQL but I'm a total PostgreSQL noob.

When I try to insert a new record into my service table - only in production - I get this:

ActiveRecord::RecordNotUnique (PGError: ERROR:  duplicate key value violates unique constraint "service_pkey"
: INSERT INTO "service" ("created_at", "name", "salon_id", "updated_at") VALUES ('2011-02-28 02:34:20.054269', 'Manicure', 1, '2011-02-28 02:34:20.054269') RETURNING "id"):
  app/controllers/services_controller.rb:46
  app/controllers/services_controller.rb:45:in `create'

I don't understand why. Shouldn't it auto-increment the PK for me?

Here's the table definition:

snip=> \d service
                                     Table "public.service"
   Column   |            Type             |                      Modifiers                       
------------+-----------------------------+------------------------------------------------------
 id         | integer                     | not null default nextval('service_id_seq'::regclass)
 name       | character varying(255)      | 
 salon_id   | integer                     | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
Indexes:
    "service_pkey" PRIMARY KEY, btree (id)

And here's the definition for that same table in development, where it works fine:

snip_development=> \d service
                                     Table "public.service"
   Column   |            Type             |                      Modifiers                       
------------+-----------------------------+------------------------------------------------------
 id         | integer                     | not null default nextval('service_id_seq'::regclass)
 name       | character varying(255)      | 
 salon_id   | integer                     | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
Indexes:
    "service_pkey" PRIMARY KEY, btree (id)

Same thing! So what could it possibly be?

Answer 1

You probably loaded up the table with data but neglected to set the current value of service_id_seq to the necessary value. You can just SELECT * FROM service_id_seq to check the current values, and use the setval function to reset it.

For example, SELECT setval('service_id_seq'::regclass, MAX(id)) FROM service; should reset the sequence to the current maximum value from the table.