Re: Sequence name with capital letters issue

On Fri, Apr 5, 2024 at 12:35 PM Thibaut BOULDOIRE <
thibaut(dot)bouldoire(at)gmail(dot)com> wrote:

> Hello,
>
> I'm using a PostgreSQL database.
> PostgreSQL 10.17 on x86_64-redhat-linux-gnu
>

Please note that while unrelated to your uissue here, PostgreSQL 10 went
end of life and is unsupported since November 2022, a year and a half ago.
You should upgrade to a supported version ASAP.

I have some sequences called like that :
> app_user_SEQ
> app_address_SEQ
> ...
>
> The issue is that when I call the next value of the sequence, I receive an
> error.
>
> When I try :
>
> SELECT nextval("app_user_SEQ");
>
> I have the following error :
>
> SQL Error [42703]: ERROR: column "app_user_SEQ' does not exist
>
> I don't understand why ? So I try to rename my sequences by writing them
> in lowercase.
> So now my sequences are like that :
> app_user_seq
> app_address_seq
> ...
>
> And now the SELECT nextval("app_user_seq"); is working.
>

This should never work. The correct way to call that is SELECT
nextval('app_user_seq').

Perhaps whatever application you are using is converting double quotes to
single quotes before actually executing the query. The error message you
show above does not come directly from psql - the SQL Error part is from
some other tool, and you didn't mention which one.

If so, you may end up having a problem, because the correct way to execute
nextval on your sequence with uppercase in it is SELECT
nextval('"app_user_SEQ"'). The single quotes are to indicate it's a string,
and then inside that string you need to double-quote the identifier to make
it case-preserving.

I also tried to add again a sequence with a part in capital letters and I
> kept the lowercase one in the DB. Now I have these 4 sequences :
> app_user_SEQ
> app_address_SEQ
> app_user_seq
> app_address_seq
>
> 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.
>
> 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 ?
>
>
Sequence names are treated the same as any other identifier. There is
nothing special about them. Identifier quoting rules are documented at
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>