Psql list all tables
PostgresqlPsqlPostgresql Problem Overview
I would like to list all tables in the liferay
database in my PostgreSQL install. How do I do that?
I would like to execute SELECT * FROM applications;
in the liferay
database. applications
is a table in my liferay db. How is this done?
Here's a list of all my databases:
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
liferay | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | liferay=CTc/postgres
lportal | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=#
Postgresql Solutions
Solution 1 - Postgresql
If you wish to list all tables, you must use:
\dt *.*
to indicate that you want all tables in all schemas. This will include tables in pg_catalog
, the system tables, and those in information_schema
. There's no built-in way to say "all tables in all user-defined schemas"; you can, however, set your search_path
to a list of all schemas of interest before running \dt
.
You may want to do this programmatically, in which case psql
backslash-commands won't do the job. This is where the INFORMATION_SCHEMA
comes to the rescue. To list tables:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
BTW, if you ever want to see what psql
is doing in response to a backslash command, run psql
with the -E
flag. eg:
$ psql -E regress
regress=# \list
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
so you can see that psql
is searching pg_catalog.pg_database
when it gets a list of databases. Similarly, for tables within a given database:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
It's preferable to use the SQL-standard, portable INFORMATION_SCHEMA
instead of the Pg system catalogs where possible, but sometimes you need Pg-specific information. In those cases it's fine to query the system catalogs directly, and psql -E
can be a helpful guide for how to do so.
Solution 2 - Postgresql
Connect to the database, then list the tables:
\c liferay
\dt
That's how I do it anyway.
You can combine those two commands onto a single line, if you prefer:
\c liferay \dt
Solution 3 - Postgresql
To see the public tables you can do
list tables
\dt
list table, view, and access privileges
\dp or \z
or just the table names
select table_name from information_schema.tables where table_schema = 'public';
Solution 4 - Postgresql
In SQL Query, you can write this code:
select table_name from information_schema.tables where table_schema='YOUR_TABLE_SCHEME';
Replace your table scheme with YOUR_TABLE_SCHEME;
Example:
select table_name from information_schema.tables where table_schema='eLearningProject';
To see all scheme and all tables, there is no need of where clause:
select table_name from information_schema.tables
Solution 5 - Postgresql
A one-line example is:
\dt schemaname.*
In your scenario:
\dt public.*
And to get a list of schemas:
\dn
Note that public
is the default schema when none is specified. Quoting the documentation:
> In the previous sections we created tables without specifying any > schema names. By default such tables (and other objects) are > automatically put into a schema named “public”. Every new database > contains such a schema.
Using \dt *.*
will output a long list of all tables in all schemas, including internal ones such as pg_catalog
. The above can help with filtering.
Solution 6 - Postgresql
This can be used in automation scripts if you don't need all tables in all schemas:
for table in $(psql -qAntc '\dt' | cut -d\| -f2); do
...
done