ERROR: duplicate key violates unique constraint "classrooms_all_pkey"
I SSH'ed into our server and ran
psql our_db_name (sidenote: psql -l lists all databases)
\d classrooms_all
which showed me two things:
"classrooms_all_pkey" PRIMARY KEY, btree (id)
This shows that the constraint being violated depends on "id" being unique.
default nextval('classrooms_all_id_seq'::regclass
This was under modifiers for the "id" column -- now I know what sequence to investigate.
SELECT * FROM classrooms_all_id_seq;
last_value is shown as 56, which I know isn't right because we have over 1000 entries in that table. Just to make sure that my thinking is correct I run
SELECT id FROM classrooms_all ORDER BY id DESC LIMIT 1;
This returns 1158, so I know that the highest id currently in the table is 1158. The incorrect value of 56 stored in the sequence is causing our error. At this point I'm not sure how to manually change the value of an autoincrement sequence so I google "postgres sequence current value" (without quotes) and this is the first result:
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
Reading through what I find there, I come across the example code:
SELECT setval('foo', 42); Next nextval will return 43
Since I already know that the highest id in the table is 1158, all I have to do is:
SELECT setval('classrooms_all_id_seq', 1158);
Problem solved!
last_value is shown as 56, which I know isn't right because we have over 1000 entries in that table. Just to make sure that my thinking is correct I run
SELECT id FROM classrooms_all ORDER BY id DESC LIMIT 1;
This returns 1158, so I know that the highest id currently in the table is 1158. The incorrect value of 56 stored in the sequence is causing our error. At this point I'm not sure how to manually change the value of an autoincrement sequence so I google "postgres sequence current value" (without quotes) and this is the first result:
http://www.postgresql.org/
Reading through what I find there, I come across the example code:
SELECT setval('foo', 42); Next nextval will return 43
Since I already know that the highest id in the table is 1158, all I have to do is:
SELECT setval('classrooms_all_id_seq'
Problem solved!