BUG #18619: uppercase column with quotation marks, gets an error without quotation marks
The following bug has been logged on the website:
Bug reference: 18619
Logged by: Kirill N
Email address: gcso@sqliteonline.com
PostgreSQL version: 17rc1
Operating system: docker 17rc1
Description:
Hello,
If you create a table with field names in uppercase and put them in
quotation marks.
then such fields can be accessed only with quotation marks.
postgres=# create table a ("b" int, "C" int);
CREATE TABLE
postgres=# select b from a;
b
---
(0 rows)
postgres=# select c from a;
ERROR: column "c" does not exist
LINE 1: select c from a;
^
postgres=# select C from a;
ERROR: column "c" does not exist
LINE 1: select C from a;
^
postgres=# select "c" from a;
ERROR: column "c" does not exist
LINE 1: select "c" from a;
^
postgres=# select "C" from a;
C
---
(0 rows)
The table structure is returned without quotation marks
postgres=# SELECT
t.table_schema,
t.table_name,
c.column_name,
c.data_type
FROM
information_schema.tables t
JOIN
information_schema.columns c
ON
t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE
t.table_type = 'BASE TABLE'
AND t.table_schema NOT IN ('information_schema', 'pg_catalog') --
Исключаем системные схемы
ORDER BY
t.table_schema,
t.table_name,
c.ordinal_position;
table_schema | table_name | column_name | data_type
--------------+------------+---------------+-------------------
public | a | b | integer
public | a | C | integer
On Mon, 16 Sept 2024 at 09:42, PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
If you create a table with field names in uppercase and put them in
quotation marks.
then such fields can be accessed only with quotation marks.
Not a bug, but a long known std deviation. Read
https://www.postgresql.org/docs/17/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
. You would have the same problem with std swapping upper and lower.
Francisco Olarte.
<div><div> </div><div><div>If this is not an error, then why does the structure return, name without quotation marks.</div><div> </div><div>Then the answer about the table structure is not correct.</div></div><div> </div></div>
Import Notes
Resolved by subject fallback
On Mon, 16 Sept 2024 at 10:53, <gcso@sqliteonline.com> wrote:
If this is not an error, then why does the structure return, name without quotation marks.
Because it does not need to.
When you send the column list a,B,"c","D" folding rules transform them
to a,b,c,D, that is "the structure return".
You are supposed to know how to send the real column names. Dura lex, sed lex.
Then the answer about the table structure is not correct.
The "table structure" IS NOT what you have to to type, is just the
real column names. If you want to define "table structure" as what you
have to type to make it work, then it is not. If you define it as
"what you send quoted and works" then it is correct ( notice with the
above definition you can use "a","b","c","D" in a query and it will
work.
Anyway, SQL has optional case folding, it was fashionable at that
time, you will have to live with it. But you will not have much
success trying to make people treat this like a bug. Normally people
just quote everything everywhere or quote nothing, and it works. If
you want it to be treated as a bug, try to find supporting evidence in
the manual and post it.
Note: I will try to stop responding in the bug list as I feel we have
already wandered off topic, feel free to send directed emails or any
other list if you want to discuss more, but be aware I am not in a
position to change this behaviour.
Francisco Olarte.
On Monday, September 16, 2024, PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 18619
Logged by: Kirill N
Email address: gcso@sqliteonline.com
PostgreSQL version: 17rc1
Operating system: docker 17rc1
Description:Hello,
If you create a table with field names in uppercase and put them in
quotation marks.
then such fields can be accessed only with quotation marks.table_schema | table_name | column_name | data_type
--------------+------------+---------------+-------------------
public | a | b | integer
public | a | C | integer
You are compelled to use quote_ident (or format code I) if you want to
interpolate stored identifiers into SQL where case-folding happens. But
the quoting is purely a SQL syntax artifact, the identifier itself does not
include the syntax quotes as part of its value.
David J.
On Monday, September 16, 2024, David G. Johnston <david.g.johnston@gmail.com>
wrote:
You are compelled to use quote_ident (or format code I) if you want to
interpolate stored identifiers into SQL where case-folding happens. But
the quoting is purely a SQL syntax artifact, the identifier itself does not
include the syntax quotes as part of its value.
It is no different than writing:
Insert into tbl (text)col) values (‘string’);
When you query tbl.text_col you see an unquoted string, not the word string
with single quotes. Because the quotes are query structure syntax, not
part of the value they surround. It’s just that identifiers use optional
double-quotes instead of the mandatory single quotes used for literals.
David J.