The Amazing "Repeat Key" Error (PostgreSQL)

Question

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

When I try to insert a new record into the service table - only during production, I get the following:

 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 do not understand why. Shouldn't he automatically increase PK for me?

Here is 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 is the definition for the 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! So what could it be?

Answer 1

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

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