Thread

1. BUG #18619: uppercase column with quotation marks, gets an error without quotation marks

   PG Bug reporting form <noreply@postgresql.org> — 2024-09-16T07:29:54Z

   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
   
   

2. Re: BUG #18619: uppercase column with quotation marks, gets an error without quotation marks

   Francisco Olarte <folarte@peoplecall.com> — 2024-09-16T08:41:02Z

   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.
   
   
   
   

3. Re: BUG #18619: uppercase column with quotation marks, gets an error without quotation marks

   David G. Johnston <david.g.johnston@gmail.com> — 2024-09-16T13:29:21Z

   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.
   

4. Re: BUG #18619: uppercase column with quotation marks, gets an error without quotation marks

   David G. Johnston <david.g.johnston@gmail.com> — 2024-09-16T13:39:09Z

   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.