pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedSequence name with capital letters issue
6+ messages / 5 participants
[nested] [flat]
* Sequence name with capital letters issue
@ 2024-04-05 09:35 Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Thibaut BOULDOIRE @ 2024-04-05 09:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
I'm using a PostgreSQL database.
PostgreSQL 10.17 on x86_64-redhat-linux-gnu
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.
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 ?
Thank you in advance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequence name with capital letters issue
@ 2024-04-05 10:50 Magnus Hagander <magnus@hagander.net>
parent: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Magnus Hagander @ 2024-04-05 10:50 UTC (permalink / raw)
To: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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_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/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequence name with capital letters issue
@ 2024-04-05 11:10 hubert depesz lubaczewski <depesz@depesz.com>
parent: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2024-04-05 11:10 UTC (permalink / raw)
To: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequence name with capital letters issue
@ 2024-04-05 12:13 Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 6+ messages in thread
From: Thibaut BOULDOIRE @ 2024-04-05 12:13 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-bugs@lists.postgresql.org; magnus@hagander.net
Hello,
Thanks for your reply.
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. "
Maybe it's the tool that I'm using ( DBeaver ) that is transforming the
capital letters into lowercase ?
Thank you for the link about the "Don't do this" wiki page.
Best regards,
Thibaut
Le ven. 5 avr. 2024 à 13:10, hubert depesz lubaczewski <depesz@depesz.com>
a écrit :
> 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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequence name with capital letters issue
@ 2024-04-05 13:58 David G. Johnston <david.g.johnston@gmail.com>
parent: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2024-04-05 13:58 UTC (permalink / raw)
To: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>; +Cc: depesz@depesz.com <depesz@depesz.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; magnus@hagander.net <magnus@hagander.net>
On Friday, April 5, 2024, Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
wrote:
>
> Maybe it's the tool that I'm using ( DBeaver ) that is transforming the
> capital letters into lowercase ?
>
No, as the docs you were pointed to say, it’s the server that folds to
lowercase any identifier that isn’t double-quoted.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sequence name with capital letters issue
@ 2024-04-05 17:34 Daniel Verite <daniel@manitou-mail.org>
parent: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Daniel Verite @ 2024-04-05 17:34 UTC (permalink / raw)
To: Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>; +Cc: depesz@depesz.com; pgsql-bugs@lists.postgresql.org; magnus@hagander.net
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-05 17:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-05 09:35 Sequence name with capital letters issue Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
2024-04-05 10:50 ` Magnus Hagander <magnus@hagander.net>
2024-04-05 11:10 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-04-05 12:13 ` Thibaut BOULDOIRE <thibaut.bouldoire@gmail.com>
2024-04-05 13:58 ` David G. Johnston <david.g.johnston@gmail.com>
2024-04-05 17:34 ` Daniel Verite <daniel@manitou-mail.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox