patternModerate
Redshift table not showing up in tables for schema?
Viewed 0 times
tablesshowingredshiftschemafornottable
Problem
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?Solution
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 -
Then run your query -
Try this -
mydb=# set search_path="$user",db;Then run your query -
mydb=# select tablename from pg_table_def where schemaname = 'db';Code Snippets
mydb=# set search_path="$user",db;mydb=# select tablename from pg_table_def where schemaname = 'db';Context
StackExchange Database Administrators Q#94096, answer score: 15
Revisions (0)
No revisions yet.