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. Continue reading “Setting the State of a Postgres Sequence” →