Unable to use ::regclass on information_schema table_name

Question

Attempting to run a simply query:

SELECT t.table_name
    FROM information_schema.tables AS t
    JOIN pg_index i
      ON i.indrelid = t.table_name::regclass

I'm getting an error:

ERROR:  relation "account" does not exist
SQL state: 42P01

From my understanding I need to cast the t.table_name with ::regclass in order to compare against indrelid. I'm assuming it's not working because the table is in another schema other than the public schema. How do I run this query to include tables in schemas other than the public schema?

db structure:

schema: public
tables: n/a
schema: core
tables: account, collection

score 2 · Answer 1 · answered Apr 26 '23 at 00:03

I was able to resolve my problem by changing my query to:

SELECT t.table_name
    FROM information_schema.tables AS t
    JOIN pg_index i
      ON i.indrelid = (quote_ident(t.table_schema) || '.' || quote_ident(t.table_name))::regclass

Using the quote_ident() to concat both parts of the schema and table name together before casting ::regclass works the same as:

'schema_name.table_name'::regclass

score 2 · Answer 2 · answered Apr 26 '23 at 03:27

The point is that reported names are case-sensitive, so some require double-quoting, and quote_ident() or format() take care of that.

Depending on what your query is supposed to do exactly, you might simply:

SELECT indrelid::regclass FROM pg_index;

To get the table name only, even when search_path does not cover it. And applying the same filters as information_schema.tables - except access privileges:

SELECT c.relname
FROM   pg_index i 
JOIN   pg_class c ON c.oid = i.indrelid
WHERE  c.relkind = ANY ('{r,v,f,p}'::"char"[])
AND    NOT pg_is_other_temp_schema(c.relnamespace)

Including access privileges:

SELECT c.relname
FROM   pg_index i 
JOIN   pg_class c ON c.oid = i.indrelid
WHERE  c.relkind = ANY ('{r,v,f,p}'::"char"[])
AND    NOT pg_is_other_temp_schema(c.relnamespace)
AND   (pg_has_role(c.relowner, 'USAGE'::text)
    OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
    OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))

Why bother? 3 - 100 x as fast. And more versatile. See:

Unable to use ::regclass on information_schema table_name

2 Answers2