Thread: 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.

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.

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.