When I do a \dt
in psql I only get a listing of tables in the current schema (public
by default).
How can I get a list of all tables in all schemas or a particular schema?
This question is related to
postgresql
postgresql-9.1
psql
Alternatively to information_schema
it is possible to use pg_tables
:
select * from pg_tables where schemaname='public';
For those coming across this in the future:
If you would like to see a list of relations for several schemas:
$psql mydatabase
mydatabase=# SET search_path TO public, usa; #schema examples
SET
mydatabase=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | counties | table | postgres
public | spatial_ref_sys | table | postgres
public | states | table | postgres
public | us_cities | table | postgres
usa | census2010 | table | postgres
You can select the tables from information_schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'public'
Source: Stackoverflow.com