PostgreSQL: Show tables in PostgreSQL
PostgreSQL: Show tables in PostgreSQL
Accepted Answer
From the psql
command line interface,
First, choose your database
\c database_name
Then, this shows all tables in the current schema:
\dt
Programmatically (or from the psql
interface too, of course):
SELECT * FROM pg_catalog.pg_tables;
The system tables live in the pg_catalog
database.
Read more… Read less…
Login as superuser:
sudo -u postgres psql
You can list all databases and users by \l
command, (list other commands by \?
).
Now if you want to see other databases you can change user/database by \c
command like \c template1
, \c postgres postgres
and use \d
, \dt
or \dS
to see tables/views/etc.
You can use PostgreSQL's interactive terminal Psql to show tables in PostgreSQL.
1. Start Psql
Usually you can run the following command to enter into psql:
psql DBNAME USERNAME
For example, psql template1 postgres
One situation you might have is: suppose you login as root, and you don't remember the database name. You can just enter first into Psql by running:
sudo -u postgres psql
In some systems, sudo command is not available, you can instead run either command below:
psql -U postgres
psql --username=postgres
2. Show tables
Now in Psql you could run commands such as:
\?
list all the commands\l
list databases\conninfo
display information about current connection\c [DBNAME]
connect to new database, e.g.,\c template1
\dt
list tables of the public schema\dt <schema-name>.*
list tables of certain schema, e.g.,\dt public.*
\dt *.*
list tables of all schemas- Then you can run SQL statements, e.g.,
SELECT * FROM my_table;
(Note: a statement must be terminated with semicolon;
) \q
quit psql
(For completeness)
You could also query the (SQL-standard) information schema:
SELECT
table_schema || '.' || table_name
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema NOT IN ('pg_catalog', 'information_schema');
First login as postgres user:
sudo su - postgres
connect to the required db:
psql -d databaseName
\dt
would return the list of all table in the database you're connected to.
Running psql with the -E flag will echo the query used internally to implement \dt and similar:
sudo -u postgres psql -E
postgres=# \dt
********* QUERY **********
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' 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;
**************************