Postgresql - relation doesn't exist

Question

I have a postgresql script which selects all information about tables and views for specific database. I'm trying to add a additional column which will display comment associated with a table or a view. But I'm getting an error SQL state: 42P01, ERROR: relation "xxx" does not exist.

select tbl.*, (select obj_description(tbl.table_name::regclass::oid, 'pg_class')) as DESCRIPTION 
from INFORMATION_SCHEMA.TABLES tbl 
where tbl.table_schema <> 'pg_catalog' and tbl.table_schema <> 'information_schema' 
order by tbl.table_schema, tbl.table_name

Unfortunately I have no idea what I'm doing wrong.

In few words: obj_description((tbl.table_schema || '.' || tbl.table_name)::regclass - use fully qualified names, including the schema name. — Abelisto
– Abelisto, Commented Aug 8, 2016 at 10:36
Actually the right answer is a combination of my comment and the answer of @LaurenzAlbe. Also you can avoid subquery in the select clause so it could be: select tbl.*, obj_description(format('%I.%I',tbl.table_schema, tbl.table_name)::regclass, 'pg_class') as DESCRIPTION from ... Good luck. — Abelisto
– Abelisto, Commented Aug 8, 2016 at 11:21

Laurenz Albe · Accepted Answer · 2016-08-08 12:50:53Z

1

This incorporates Abelisto's comments above.

You should use

obj_description(
   (quote_ident(tbl.table_name) || '.' || quote_ident(tbl.table_name))::regclass::oid,
   'pg_class'
)

instead of

(select obj_description(tbl.table_name::regclass::oid, 'pg_class'))

I guess that some of your table names are not all lower case.

edited Aug 8, 2016 at 12:50

answered Aug 8, 2016 at 9:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgresql - relation doesn't exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related