Re: Sequence name with capital letters issue

Thibaut BOULDOIRE wrote:

> Sorry, yes, I executed SELECT nextval('app_user_SEQ'); with simple quotes,
> no double quotes.
> And the error message is " the relation "app_user_seq" does not exist. "

The syntax that would work is:
SELECT nextval('"app_user_SEQ"');
with two levels of quoting, single quotes at the outer level
and double quotes at the inner level.

The reason for this is non-trivial: nextval() takes an argument
of type regclass, which is an "OID alias type" as described here:
https://www.postgresql.org/docs/current/datatype-oid.html

The string '"app_user_SEQ"' is interpreted and cast into
and OID with the rules of the regclass type, and in particular, the
casefolding rule described as follows:

"The input functions for these types allow whitespace between tokens,
and will fold upper-case letters to lower case, except within double
quotes; this is done to make the syntax rules similar to the way
object names are written in SQL"

This is why the bit a of advice at
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names

"if you use uppercase characters in your table or column names you have to
either always double quote them or never double quote them"

also applies to the argument of nextval().

You could write nextval('app_user_SEQ') if it had been created with
CREATE SEQUENCE app_user_SEQ;
instead of
CREATE SEQUENCE "app_user_SEQ"

Also sometimes users create the sequence through the input form of an
SQL app, and IIRC some apps implicitly add the double quotes.
It's not intuitive to have to name the objects down-case
to later refer to them unquoted camel-case, and yet that's what
we must do in these creation forms.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite