PostgreSQL: Show tables in PostgreSQL
What's the equivalent to show tables
(from MySQL) in PostgreSQL?
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.
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');