Talk:Fixing Sequences

From PostgreSQL wiki

Revision as of 17:59, 18 November 2013 by Crashcover (Talk | contribs)

Jump to: navigation, search

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).

--Crashcover (talk) 17:59, 18 November 2013 (UTC)

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?

Personal tools