From PostgreSQL wiki
Can somebody update this to support sequences with uppercase characters in them? Perhaps it's not a best practice, but some of my id columns have uppercase letters, which causes this to fail.
Instead of doing something like:
'"my_column_WITH_uppercase"', it's doing 'my_column_WITH_uppercase', which doesn't work. I'm not sure how to make this work, but I've struggled with this twice now (with months intervening).
I've modified the query to this:
SELECT 'SELECT SETVAL(' ||quote_literal(quote_ident(S.relname))|| ', (CASE WHEN COUNT(' ||quote_ident(C.attname)|| ') = 0 THEN ''0'' ELSE MAX(' ||quote_ident(C.attname)|| ') END) ) FROM ' ||quote_ident(T.relname)|| ';' FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C WHERE S.relkind = 'S' AND S.oid = D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND D.refobjsubid = C.attnum ORDER BY S.relname;
Reason: When MAX(X) delivers a null (cause of a empty table) SETVAL doesn't reset the sequence last_value to 0. Should we update the query in the wiki? Or is there another solution for this problem?