Re: 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