Re: Sequence name with capital letters issue
| От | Magnus Hagander |
|---|---|
| Тема | Re: Sequence name with capital letters issue |
| Дата | |
| Msg-id | CABUevEywJxehedDpP4kD-kAdyDiiKFPkyUt85itHxXbxk-mpSQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | [MASSMAIL]Sequence name with capital letters issue (Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>) |
| Список | pgsql-bugs |
On Fri, Apr 5, 2024 at 12:35 PM Thibaut BOULDOIRE <thibaut.bouldoire@gmail.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_SEQapp_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 existI 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_seqapp_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_SEQapp_address_SEQapp_user_seqapp_address_seqBut 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
В списке pgsql-bugs по дате отправления: