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?
The current version of the query (as on July 19) produces statements like this:
SELECT SETVAL('public.core__users_id_seq', COALESCE(MAX(id, 1), MAX(id) IS NOT NULL) ) FROM public.core__users;
The MAX function is called with two arguments, which is an error. IMO, the generated query should look like this:
SELECT SETVAL('public.core__users_id_seq', COALESCE(MAX(id), 1)) FROM public.core__users;
I will attempt to produce a fixed query.