{"id":1365,"date":"2011-08-08T15:58:34","date_gmt":"2011-08-08T20:58:34","guid":{"rendered":"http:\/\/www.kickflop.net\/blog\/?p=1365"},"modified":"2011-08-08T15:58:35","modified_gmt":"2011-08-08T20:58:35","slug":"adjusting-a-postgresql-sequence","status":"publish","type":"post","link":"https:\/\/www.kickflop.net\/blog\/2011\/08\/08\/adjusting-a-postgresql-sequence\/","title":{"rendered":"Adjusting a PostgreSQL Sequence"},"content":{"rendered":"<p>I recently had the unfortunate task at work of copying most of a production PostgreSQL database to a development server.  The whole database would have been simple.  It&#8217;s the &#8220;most of&#8221; part that made things messy.  At any rate, I ended up in a position where I needed to make various incorrect &#8220;sequence&#8221; definitions match their respective tables.  For example, the maximum <code>id<\/code> in table <code>principals<\/code> was 18133 and the sequence used for that primary key was currently at, well, 1.<\/p>\n<p>I&#8217;d never ever delved into this sort of thing before (I had to look up what a PostgreSQL sequence was) as I&#8217;m not a DBA by trade.  I also won&#8217;t likely have to do this again for quite some time, so here are my notes:<\/p>\n<p>Query the last value for the sequence (well call this <em>SEQLAST<\/em> below, and assume it returned 3):<\/p>\n<pre>SELECT last_value FROM mytable_sequence;<\/pre>\n<p>Query for the largest <code>id<\/code> (our primary key, we&#8217;ll call this <em>LARGESTID<\/em> below and assume it returned 18133):<\/p>\n<pre>SELECT id FROM mytable ORDER BY id DESC;<\/pre>\n<p>If <em>SEQLAST<\/em> and <em>LARGESTID<\/em> match, you&#8217;re all set.  If they don&#8217;t, you need to adjust <code>mytable_sequence<\/code> so that the next primary key issued is <em>LARGESTID<\/em> + 1 for <code>mytable<\/code>.  You do this with the <code>setval<\/code> sequence function.<\/p>\n<pre>SELECT setval('mytable_sequence', 18133);<\/pre>\n<p>This reads as, &#8220;Set the last value of <code>mytable_sequence<\/code> to 18133 and increment before handing out the next number when <code>nextval()<\/code> is called for this sequence.&#8221;  The incrementing is implied, though you could also specify the clearer and functionally equivalent statement via:<\/p>\n<pre>SELECT setval('mytable_sequence', 18133, true);<\/pre>\n<p>You could also specify &#8220;Set the next value of <code>mytable_sequence<\/code> to 18134 and do not increment before handing out the next number when <code>nextval()<\/code> is called for this sequence.&#8221;<\/p>\n<pre>SELECT setval('mytable_sequence', 18134, false);<\/pre>\n<p>Reference: <a href=\"http:\/\/www.google.com\/search?hl=en&#038;safe=off&#038;q=site%3Apostgresql.org+postgresql+setval&#038;oq=site%3Apostgresql.org+postgresql+setval&#038;aq=f&#038;aqi=&#038;aql=&#038;gs_sm=e&#038;gs_upl=8000l13475l0l13730l20l15l0l0l0l2l180l1683l6.9l15l0\">Google Search: site:postgresql.org postgresql setval<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently had the unfortunate task at work of copying most of a production PostgreSQL database to a development server.&hellip;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,11],"tags":[],"class_list":["post-1365","post","type-post","status-publish","format-standard","hentry","category-database","category-sysadmin"],"_links":{"self":[{"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/posts\/1365","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/comments?post=1365"}],"version-history":[{"count":4,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/posts\/1365\/revisions"}],"predecessor-version":[{"id":1369,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/posts\/1365\/revisions\/1369"}],"wp:attachment":[{"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/media?parent=1365"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/categories?post=1365"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kickflop.net\/blog\/wp-json\/wp\/v2\/tags?post=1365"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}