15

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';
 tablename 
-----------
(0 rows)

mydb=# \d db.some_table
                    Table "db.some_table"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
...correct info shows up here...
...but nothing showed up above?
Some Guy
  • 251
  • 1
  • 2
  • 4

3 Answers3

15

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';
Robert Rodriguez
  • 183
  • 5
Kamlesh Gallani
  • 281
  • 2
  • 7
1

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

Bill SY
  • 11
  • 1
1

In some cases set search_path TO '$user','schema_1','schema_2'; might not work.
Running ALTER USER <youruser> SET SEARCH_PATH to '$user','schema_1','schema_2' should solve it.

Please note that this will permanently change the search path, so in case you want the original search path settings you can run the above ALTER command again, with the default search path.

Yankee
  • 131
  • 5