Date: Fri, 5 Apr 2024 13:10:04 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Sequence name with capital letters issue
Message-ID: <Zg_cDH8BnlVKC2ly@depesz.com>
Reply-To: depesz@depesz.com
References: 
 <CAJG-3PT3Hn6pq7hGpWLSpkK8bKCvv3gG4OmpkDJfD1+S6HA_JA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CAJG-3PT3Hn6pq7hGpWLSpkK8bKCvv3gG4OmpkDJfD1+S6HA_JA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Zg_cDH8BnlVKC2ly%40depesz.com>
Precedence: bulk

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