On Redshift, why doesn't my table show up in the following query? It definitely exists, as shown by the next query I run. I want a way to list all tables for a schema:

mydb=# select distinct(tablename) from pg_table_def where schemaname = 'db';
(0 rows)

mydb=# \d db.some_table
                    Table "db.some_table"
     Column      |            Type             | Modifiers 
...correct info shows up here...
...but nothing showed up above?
  • 4
    Unrelated to your question, but distinct is not a function. I suggest you remove the parentheses to avoid confusion. On second thought you might as well remove distinct it self since there cannot be two tables with the same name in one schema. – Lennart Mar 1 '15 at 9:03
  • What is the result of: select schemaname, tablename from pg_table_def ? – Lennart Mar 1 '15 at 9:07
  • Are you sure your schema-name is "db", because it looks like a "database name"?. – Senthil Jul 27 '15 at 9:28

PG_TABLE_DEF in Redshift only returns information about tables that are visible to the user, in other words, it will only show you the tables which are in the schema(s) which are defined in variable search_path. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schema(s).

Try this -

mydb=# set search_path="$user",db;

Then run your query -

mydb=# select tablename from pg_table_def where schemaname = 'db';
  • Please edit your answer to provide more information than 'do this'. – RLF Aug 29 '16 at 12:40

PG_TABLE_DEF will only return information for tables in schemas that are included in the search path. Link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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