Re: autoupdate sequences after copy

> I just run a script to update them after importing data.  Something
> like this... (not a real script...)
>
> while read tablename
> do
>     echo "select setval('${tablename}_${tablename}_seq', \
>         (select max(${tablename}id) from $tablename))" | psql database
> done
> < tablenames.txt
>
> Of course, this assumes you allowed the default sequence names to be
> created via SERIAL and that you created the primary keys as
> <tablename>id.  You might need a text file with table, key, and
> sequence names, but this is likely easier than issuing a bunch of psql
> commands by hand.
>

You can get a list of you sequences with

CREATE VIEW public.sequences AS
SELECT
 nspname,
 pg_get_userbyid(c.relowner) AS sequenceowner,
 c.relname AS sequencename
FROM (pg_class c JOIN pg_namespace t2 ON ((t2.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char")
ORDER BY nspname, pg_get_userbyid(c.relowner), c.relname;

Maybe you use the output of this view to create a table in which you add
columns for the table name and column name, and then go through the kind
of iteration suggested above.


~Berend Tober