While migrating data for ipythonblocks.org I ran into an issue that stumped me for about a day: I could read data from my database after the migration, but not insert anything. When I tried, Postgres complained that there I was trying to insert a duplicate primary key. The twist, though, was that I wasn’t specifying a primary key, I expected Postgres to automatically generate and insert a new, valid key. What I ultimately discovered was that because the data I had migrated already contained primary key values, the Postgres sequence created for the table primary key column had not been advanced to the point where it would generate new key values that weren’t already in the table. (A sequence is a stateful number generator kind of like Python’s range.) I had to manually set the sequence state using Postgres’ sequence manipulation functions. This post contains a demonstration of this problem and the solution. SQL was run against Postgres 9.6.3 using pgcli.

First I’ll create a demo table with an integery primary key column (defined as a sequence using Postgres’ serial type) and a text column:

CREATE TABLE demo (
  id BIGSERIAL PRIMARY KEY,
  value TEXT);

Then I’ll insert some data that includes values for the primary key (as when I was migrating data from database to another):

INSERT INTO demo (id, value) VALUES
  (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

As expected, at this point the table has 5 rows:

SELECT * FROM demo;
+------+---------+
| id   | value   |
|------+---------|
| 1    | A       |
| 2    | B       |
| 3    | C       |
| 4    | D       |
| 5    | E       |
+------+---------+

Now I’ll try to insert a new row without specifying a value for the id column. This is how data is most often inserted into a table with a primary key so that the database can generate a new valid key.

INSERT INTO demo (value) VALUES ('F');
duplicate key value violates unique constraint "demo_pkey"
DETAIL:  Key (id)=(1) already exists.

This fails complaining that the key id=1 already exists, and of course it does. But why didn’t Postgres know that and generate a new key of 6? To answer that question I need to examine the sequence backing the primary key of the demo table. We can get a closer look at the table definition using the psql/pgcli \d command:

\d demo;
+----------+--------+----------------------------------------------------+
| Column   | Type   | Modifiers                                          |
|----------+--------+----------------------------------------------------|
| id       | bigint |  not null default nextval('demo_id_seq'::regclass) |
| value    | text   |                                                    |
+----------+--------+----------------------------------------------------+
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)

The default nextval('demo_id_seq'::regclass) in the Modifiers column means that the default value for the id column is the result of calling the nextval function on the 'demo_id_seq' sequence. nextval is one of Postgres’ sequence manipulation functions, currval is another that allows you to see the value that was returned the last time nextval was called:

SELECT currval('demo_id_seq');
+-----------+
| currval   |
|-----------|
| 1         |
+-----------+

As we saw above when I tried to insert the value 'F' into the table the 'demo_id_seq' sequence tried to give it a key of 1. It turns out that inserting rows into the table that already have values for the primary key column doesn’t advance the sequence that provides values for that column when a default is required. This is something you’ll need to watch out for any time you’re inserting data that includes a value for the primary key column. The solution in my case was to set the current value of the primary key sequence to the maximum value already present using the setval function:

SELECT setval('demo_id_seq', (SELECT max(id) FROM demo));
+----------+
| setval   |
|----------|
| 5        |
+----------+

Using currval again confirms the change:

SELECT currval('demo_id_seq');
+-----------+
| currval   |
|-----------|
| 5         |
+-----------+

nextval('demo_id_seq') should now return 6 and I can retry inserting new data:

INSERT INTO demo (value) VALUES ('F');
SELECT * FROM demo;
+------+---------+
| id   | value   |
|------+---------|
| 1    | A       |
| 2    | B       |
| 3    | C       |
| 4    | D       |
| 5    | E       |
| 6    | F       |
+------+---------+

It worked! And the current value of the sequence is 6 as expected:

SELECT currval('demo_id_seq');
+-----------+
| currval   |
|-----------|
| 6         |
+-----------+

---

SELECT 'Thanks for reading, you rock!' as signoff;
+-------------------------------+
| signoff                       |
|-------------------------------|
| Thanks for reading, you rock! |
+-------------------------------+