Fixing Sequences
Updating sequence values from table
A common problem when copying or recreating a database is that database sequences are not updated just by inserting records in the table that sequence is used in. If you want to make your sequences all start just after whatever values are already there, it's possible to do that for most common configurations like this:
SELECT'SELECT SETVAL('||quote_literal(quote_ident(sequence_namespace.nspname)||'.'||quote_ident(class_sequence.relname))||', COALESCE(MAX('||quote_ident(pg_attribute.attname)||'), 1) ) FROM '||quote_ident(table_namespace.nspname)||'.'||quote_ident(class_table.relname)||';'FROMpg_dependINNERJOINpg_classASclass_sequenceONclass_sequence.oid=pg_depend.objidANDclass_sequence.relkind='S'INNERJOINpg_classASclass_tableONclass_table.oid=pg_depend.refobjidINNERJOINpg_attributeONpg_attribute.attrelid=class_table.oidANDpg_depend.refobjsubid=pg_attribute.attnumINNERJOINpg_namespaceastable_namespaceONtable_namespace.oid=class_table.relnamespaceINNERJOINpg_namespaceASsequence_namespaceONsequence_namespace.oid=class_sequence.relnamespaceORDERBYsequence_namespace.nspname,class_sequence.relname;Usage would typically work like this:
- Save this to a file, say 'reset.sql'
- Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example:
psql -Atq -f reset.sql -o temp psql -f temp rm temp
There are a few limitations to this snippet of code you need to be aware of:
- It only works on sequences that are owned by a table. If your sequences are not owned, run the following script first:
Fixing sequence ownership
This script changes sequences with OWNED BY to the table and column they're referenced from. NB! Sequences that are referenced by multiple tables or columns are ignored.
(Parts of query shamelessly stolen from OmniTI's Tasty Treats repository by Robert Treat)
select'ALTER SEQUENCE '||quote_ident(min(schema_name))||'.'||quote_ident(min(seq_name))||' OWNED BY '||quote_ident(min(table_name))||'.'||quote_ident(min(column_name))||';'from(selectn.nspnameasschema_name,c.relnameastable_name,a.attnameascolumn_name,substring(d.adsrcfromE'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)')asseq_namefrompg_classcjoinpg_attributeaon(c.oid=a.attrelid)joinpg_attrdefdon(a.attrelid=d.adrelidanda.attnum=d.adnum)joinpg_namespacenon(c.relnamespace=n.oid)wherehas_schema_privilege(n.oid,'USAGE')andn.nspnamenotlike'pg!_%'escape'!'andhas_table_privilege(c.oid,'SELECT')and(nota.attisdropped)andd.adsrc~'^nextval')seqgroupbyseq_namehavingcount(*)=1;This snippet finds orphaned sequences that aren't owned by any column. It can be helpful to run this, to double-check that the above query did its job right.
selectns.nspnameasschema_name,seq.relnameasseq_namefrompg_classasseqjoinpg_namespacenson(seq.relnamespace=ns.oid)whereseq.relkind='S'andnotexists(select*frompg_dependwhereobjid=seq.oidanddeptype='a'andclassid='pg_class'::regclass)orderbyseq.relname;
