I create a table in Redshift. When I tried to search for the table definition, I get back results from information_schema.columns
by running the following query:
select * from information_schema.columns
where table_name = 'table' and table_schema='schema'
However, when I run a query against pg_catalog.pg_table_def
, I don't get back any result.
select * from pg_catalog.pg_table_def
where tablename = 'table' and schemaname = 'schema'
Could anyone help me understand why this is happening? The table is created and owned by the account I'm using.
Best Answer
Check
show search_path;
to make sure you are on the current path where the table was created. As stated on the original AWS redshift documentation.to get what you want you should run
set search_path to '$user', '<#your_schema#>'; select * from pg_catalog.pg_table_def where tablename = '<#your_table#>';
Hope that helps.