How to make sql query on table name with space separated in Postgres?

Question

I'm using postgres database and it has table with name say 'System Tenant'. Now I want to make query on it, I do -

select * from "System Tenant";

but it results into error -

ERROR:  relation "System Tenant" does not exist
LINE 1: select * from "System Tenant"
                       ^

Could you please suggest how I can resolve it?

What name exactly does \d show you in psql (or select tablename from pg_tables where lower(tablename) like 'system%')? I guess it might be "system tenant" or "SYSTEM TENANT" or something similar - as soon as you use double quotes the name is case sensitive. — user330315
– user330315, Commented May 16, 2018 at 12:35
@user5542464: the backslash is not an "escape character" in SQL strings — user330315
– user330315, Commented May 16, 2018 at 12:37

Vao Tsun · Accepted Answer · 2018-05-16 12:17:06Z

3

lets say:

so=# create schema t;
CREATE SCHEMA
so=# create table t."Bad Name"();
CREATE TABLE
so=# create table "b@d Name"();
CREATE TABLE

now find all:

so=# select oid::regclass from pg_class where relname ilike '% name%';
     oid
--------------
 t."Bad Name"
 "b@d Name"
(2 rows)

and use exactly as it is listed:

so=# select * from t."Bad Name";
--
(0 rows)

or

so=# select * from "b@d Name";
--
(0 rows)

answered May 16, 2018 at 12:17

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1