Talk:Fixing Sequences

From PostgreSQL wiki

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?

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.

Kushalkumaran (talk) 08:41, 19 July 2014 (UTC)

Personal tools