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
  • 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 01 '15 at 09:03
  • What is the result of: `select schemaname, tablename from pg_table_def` ? – Lennart Mar 01 '15 at 09:07
  • Are you sure your schema-name is "db", because it looks like a "database name"?. – Senthil Jul 27 '15 at 09:28

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
  • Please edit your answer to provide more information than 'do this'. – RLF Aug 29 '16 at 12:40
  • I think this is the right direction, however I was unable to change `search_path`. After setting it, it still shows `$user,public`. – dz902 May 27 '21 at 10:39
1

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

Bill SY
  • 11
  • 1
0

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
  • 121
  • 4