Find schema name given table name in Redshift

Is it possible to retrieve the schema name given the table name in redshift and return an error if table is not found?

  SELECT * FROM PG_TABLE_DEF WHERE tablename = 'tablename';

doesn’t seem to work.

Answer

pg_table_def lets you see only the tables that are visible to your curent user – which, presumably, is not the case of the table you are searching for.

You could use pg_tables instead (this is originally a Postgre catalog view, that is documented as partially accessible in Redshift):

select schemaname from pg_tables where tablename = 'mytable';

You might also be facing some case issue, if your table was created with a case-sensitive identifier. In that case:

where lower(tablename) = 'mytable';