3

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.

1 Answer 1

3

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.

PG_TABLE_DEF only returns information about tables that are visible to the user.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.