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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With