List tables in a PostgreSQL schema
Solution 1
In all schemas:
=> \dt *.*
In a particular schema:
=> \dt public.*
It is possible to use regular expressions with some restrictions
\dt (public|s).(s|t)
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | s | table | cpn
public | t | table | cpn
s | t | table | cpn
Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for
.
which is taken as a separator as mentioned above,*
which is translated to the regular-expression notation.*
,?
which is translated to.
, and$
which is matched literally. You can emulate these pattern characters at need by writing?
for.
,(R+|)
forR*
, or(R|)
forR?
.$
is not needed as a regular-expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words,$
is automatically appended to your pattern). Write*
at the beginning and/or end if you don't wish the pattern to be anchored. Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally. Also, the regular expression special characters are matched literally in operator name patterns (i.e., the argument of\do
).
Solution 2
You can select the tables from information_schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'public'
Solution 3
Alternatively to information_schema
it is possible to use pg_tables
:
select * from pg_tables where schemaname='public';
Solution 4
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
Solution 5
If you are interested in listing all tables in a particular schema, I found this answer relevant :
SELECT table_schema||'.'||table_name AS full_rel_name
FROM information_schema.tables
WHERE table_schema = 'yourschemaname';
Nyxynyx
Hello :) I have no formal education in programming :( And I need your help! :D These days its web development: Node.js Meteor.js Python PHP Laravel Javascript / jQuery d3.js MySQL PostgreSQL MongoDB PostGIS
Updated on March 29, 2021Comments
-
Nyxynyx about 3 years
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?
-
Matt Bannert over 10 yearsvery helpful if your interface doesn't support the shortcuts. thanks.
-
Frozen Flame over 8 yearsSimply
\dt
is equivolent to\dt public.*
, am I right? -
Josh Brown over 8 yearsThis is also nice because you could do something like select table_schema, table_name from information_schema.tables where table_name like '%whatever%'; if you need to know which schema the table is located in. Not sure you can do that with \dt
-
Carlos2W over 8 yearsThank you, it works on Amazon Redshift and \dt (accepted answer) doesn't.
-
James M. Lay over 8 yearsHow about, say, two specific tables in a specific schema? Like
\dt public.user_info, public.user_scope
? -
James M. Lay over 8 yearsNevermind, it's easier just to do
\dt public.a; \dt public.b;
on one line. -
Grant Humphries about 8 yearsnote that if you only want the table name is the resultant query it is
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
-
Davos over 6 yearsThis is the most generally useful answer. information_schema is defined in SQL Standards, and available on most databases that comply
-
John Crawford over 6 yearsFound a permissions issue with
information_schema
not listing items from thepublic
schema, but thepg_tables
method worked nicely. Many thanks! -
mehmet over 5 yearsit is kind of implicit.. If \dt gives only "public" tables, one would not expect anything more via regular expressions..
-
Catfish almost 5 yearsAnd if your table name contains capital letters you have to quote the schema name like this
\dt "MySchema".*
-
Lukas Juhrich over 4 years@FrozenFlame It is not! By default it shows whatever is in your
search_path
, and that defaults to"$user", public.*
. Consequently,set search_path=s; \dt
is going to list all tables in the schemas
. -
SzieberthAdam almost 3 yearsTo exclude views, put
AND table_type = 'BASE TABLE'
to the where clause. -
SzieberthAdam almost 3 yearsTo exclude views, put
AND table_type = 'BASE TABLE'
to the where clause.