SQL to querying a table with a dollar sign

Question

I'm using the Looker Dashboarding software (see: looker.com). It creates temporary tables in your database's looker_scratch schema with long names, each containing a dollar symbol.

These are straightforward to query using the "SQL Runner" in Looker itself, which somehow is able to escape the dollar symbol, but I can't query them using a 3rd-party SQL client.

I'm trying to query this table:

SELECT *
FROM looker_scratch.LR$5UA5D3XQDBPAYU0Q9FLFE_test

but get the error:

the # of binded parameters < the # of parameter markers

How can I query the table?

I've tried:

...FROM looker_scratch."LR$5UA5D3XQDBPAYU0Q9FLFE_test" - says the relation does not exist
...FROM looker_scratch."LR\$5UA5D3XQDBPAYU0Q9FLFE_test" - says the relation does not exist
...FROM looker_scratch.$LR\$5UA5D3XQDBPAYU0Q9FLFE_test$ - says syntax error
...FROM looker_scratch.$$LR\$5UA5D3XQDBPAYU0Q9FLFE_test$$ - says syntax error
...FROM looker_scratch.E'LR\$5UA5D3XQDBPAYU0Q9FLFE_test' - says syntax error

I would expect `select oid::regclass from pg_class where relname ilike '%5ua5d%';` to show you how exactly you should query the identifier — Vao Tsun, May 03 '18 at 19:50

score 4 · Accepted Answer · answered May 03 '18 at 19:51

4

try selecting exact identifier by pattern:

select oid::regclass from pg_class where relname ilike '%5ua5d%';

E.g:

so=# create table t."WeirdMix$" ();
CREATE TABLE
Time: 55.750 ms
so=# select oid::regclass from pg_class where relname ilike '%mix%';
      oid
---------------
 t."WeirdMix$"
(1 row)

Time: 90.814 ms

answered May 03 '18 at 19:51

Vao Tsun

47,234
13
100
132

Fantastic! The issue in the end was really because Looker was telling me the table was in upper case whereas in information_schema.tables the tables are all lower case. Why didn't I think of that? *facepalm*. Thanks anyway :D – LittleBobbyTables May 04 '18 at 14:11

SQL to querying a table with a dollar sign

1 Answers1