Psql list all tables

问题:

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=# 

回答1:

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.

回答2:

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

回答3:

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';

回答4:

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

回答5:

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

回答6:

You can type \? to get information on all the commands supported in psql.