PostgreSQL Sequence Updates

January 7, 2012

I had a problem with PostgreSQL pgdump recently. My setval() calls were all set to '1'.

I wrote this script to fix things:

#!/usr/bin/env python
DB_NAME = 'my_db'

from subprocess import Popen, PIPE
import re

exclude = [ 'tablename', 'rows' ]
tp = re.compile( '[^a-z_]' )
query = (
    "-c SELECT tablename FROM pg_tables "
    "WHERE tablename NOT LIKE 'pg_%' "
    "AND tablename NOT LIKE 'sql_%' "
    "ORDER BY tablename"
)
ts = Popen(
    [ "/usr/bin/psql", DB_NAME, query ],
    stdout=PIPE
).communicate()[ 0 ].split( ' ' )

tables = []
for t in ts:
    t = tp.sub( '', t )
    if len( t ) == 0 or t in exclude:
        continue
    tables.append( t )

for t in tables:
    sql = (
        "SELECT pg_catalog.setval( "
        "pg_get_serial_sequence( '%s', 'id' ), "
        "( SELECT MAX( id ) FROM %s ) + 1 );"
    ) % ( t, t )
    print Popen(
        [ "/usr/bin/psql", DB_NAME, "-c %s" % sql ],
        stdout=PIPE
    ).communicate()[ 0 ]

postgresql python