Обсуждение: [MASSMAIL]Sequence name with capital letters issue

On Fri, Apr 05, 2024 at 11:35:45AM +0200, Thibaut BOULDOIRE wrote:
> So now my sequences are like that :
> app_user_seq
> app_address_seq
> And now the SELECT nextval("app_user_seq"); is working.

Are you sure this is what you ran?
Because this causes the same error:

#v+
depesz=# create sequence app_user_seq;
CREATE SEQUENCE

depesz=# select nextval("app_user_seq");
ERROR:  column "app_user_seq" does not exist
LINE 1: select nextval("app_user_seq");
                       ^
depesz=# select nextval('app_user_seq');
 nextval
---------
       1
(1 row)
#v-

There is difference between " and ' :)

You *can* use upper case letters in seq names, but it will not be pretty:

#v+
depesz=# create sequence "app_user_XXX";
CREATE SEQUENCE

depesz=# select nextval('app_user_XXX');
ERROR:  relation "app_user_xxx" does not exist
LINE 1: select nextval('app_user_XXX');
                       ^
depesz=# select nextval('"app_user_XXX"');
 nextval
---------
       1
(1 row)
#v-

> But now, something interesting happened.
> When I call this query : SELECT nextval("app_user_SEQ"); , it's the
> app_user_seq sequence that is incremented and not the app_user_SEQ.

This is most likely some unrelated thing. Especially since calling nextval(X),
where X is in "quotes" is going to fail virtually always.

> I didn't find in the documentation something that mentioned this issue
> regarding sequence names with capital letters.
> Is it a bug ? or is it something that I didn't find in the documentation ?

I can't comment on the "wrong sequence gets incremented", but generally
you didn't read the "Don't do this" wiki page:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names

Best regards,

depesz

Maybe it's the tool that I'm using ( DBeaver ) that is transforming the capital letters into lowercase ?

    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