Cannot query postgres when tables have complex names/case

[ilsaloving]

This is related to issues #11042 and #20421.

Trino is forcing identifiers to lower case, and it doesn't handle identifiers with unusual symbols. (eg: -)

Normally these are fine if you wrap them in quotes (eg: SELECT "my-column" from "MyTable"), but it seems like trino strips those out as well.

[ebyhr]

@ilsaloving Could you share the entire steps to reproduce the symbol issue? I can't reproduce it locally.

CREATE TABLE postgresql.tpch.test AS SELECT 1 "my-column";

SELECT * FROM postgresql.tpch.test;
 my-column
-----------
         1
(1 row)

SELECT "my-column" FROM postgresql.tpch.test;
 my-column
-----------
         1
(1 row)

[ilsaloving]

Using dbeaver (because my trino connection is R/O), I connect to postgres itdw.public and do:
create table "MyTable" as select 1 "my-column";

From trino, I then do:

trino> use itdw.public;
USE
trino:public> show tables;
  Table
---------
 mytable
(1 row)

Query 20241021_132633_00005_kbmuj, FINISHED, 1 node
Splits: 7 total, 7 done (100.00%)
0.31 [1 rows, 23B] [3 rows/s, 75B/s]

trino:public> select * from mytable;
Query 20241021_132651_00006_kbmuj failed: line 1:15: Table 'itdw.public.mytable' does not exist
select * from mytable

I tried with the columns:
create table "anothertable" as select 1 "my-column", 1 "MyColumn";

and that appeared to work. Or at least, Trino is doing some internal mapping between case-insensitive and case-sensitive:

trino:public> select * from anothertable;
 my-column | mycolumn
-----------+----------
         1 |        1
(1 row)

Query 20241021_133230_00012_kbmuj, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.21 [1 rows, 0B] [4 rows/s, 0B/s]

trino:public> select mycolumn from anothertable;
 mycolumn
----------
        1
(1 row)

Query 20241021_133245_00013_kbmuj, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.31 [1 rows, 0B] [3 rows/s, 0B/s]

So it looks like whatever Trino is doing with column names, also needs to be done with table names.

[ebyhr]

I understand this is not a new issue. Please take a look at https://trino.io/docs/current/connector/postgresql.html#case-insensitive-matching & #17.

[ilsaloving]

Thank you. I have no idea how I missed that.