PostgreSQL: Show tables in PostgreSQL
PostgresqlPostgresql Problem Overview
What's the equivalent to show tables
(from MySQL) in PostgreSQL?
Postgresql Solutions
Solution 1 - 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.
Solution 2 - Postgresql
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.
Solution 3 - Postgresql
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
Solution 4 - Postgresql
(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');
Solution 5 - Postgresql
-
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.
Solution 6 - Postgresql
Login as a superuser so that you can check all the databases and their schemas:-
sudo su - postgres
Then we can get to postgresql shell by using following command:-
psql
You can now check all the databases list by using the following command:-
\l
If you would like to check the sizes of the databases as well use:-
\l+
Press q
to go back.
Once you have found your database now you can connect to that database using the following command:-
\c database_name
Once connected you can check the database tables or schema by:-
\d
Now to return back to the shell use:-
q
Now to further see the details of a certain table use:-
\d table_name
To go back to postgresql_shell press \q
.
And to return back to terminal press exit
.
Solution 7 - Postgresql
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;
**************************
Solution 8 - Postgresql
use only see a tables
=> \dt
if want to see schema tables
=>\dt+
if you want to see specific schema tables
=>\dt schema_name.*
Solution 9 - Postgresql
If you only want to see the list of tables you've created, you may only say:
\dt
But we also have PATTERN
which will help you customize which tables to show. To show all including pg_catalog
Schema, you can add *
.
\dt *
If you do: \?
>\dt[S+] [PATTERN] list tables
Solution 10 - Postgresql
First Connect with the Database using following command
\c database_name
And you will see this message - You are now connected to database database_name
. And them run the following command
SELECT * FROM table_name;
In database_name and table_name just update with your database and table name
Solution 11 - Postgresql
If you are using pgAdmin4 in PostgreSQL, you can use this to show the tables in your database:
select * from information_schema.tables where table_schema='public';
Solution 12 - Postgresql
select
*
from
pg_catalog.pg_tables
where
schemaname != 'information_schema'
and schemaname != 'pg_catalog';
Solution 13 - Postgresql
Note that \dt
alone will list tables in the public schema of the database you're using. I like to keep my tables in separate schemas, so the accepted answer didn't work for me.
To list all tables within a specific schema, I needed to:
-
Connect to the desired database:
psql mydb
-
Specify the schema name I want to see tables for after the
\dt
command, like this:\dt myschema.*
This shows me the results I'm interested in:
List of relations
Schema | Name | Type | Owner
----------+-----------------+-------+----------
myschema | users | table | postgres
myschema | activity | table | postgres
myschema | roles | table | postgres
Solution 14 - Postgresql
\dt (no * required) -- will list all tables for an existing database you are already connected to. Also useful to note:
\d [table_name] -- will show all columns for a given table including type information, references and key constraints.
Solution 15 - Postgresql
You can list the tables in the current database with \dt
.
Fwiw, \d tablename
will show details about the given table, something like show columns from tablename
in MySQL, but with a little more information.
Solution 16 - Postgresql
\dt
will list tables, and \pset pager off
shows them in the same window, without switching to a separate one. Love that feature to death in dbshell.
Solution 17 - Postgresql
The most straightforward way to list all tables at command line is, for my taste :
psql -a -U <user> -p <port> -h <server> -c "\dt"
For a given database just add the database name :
psql -a -U <user> -p <port> -h <server> -c "\dt" <database_name>
It works on both Linux and Windows.
Solution 18 - Postgresql
Those steps worked for me with PostgreSQL 13.3
and Windows 10
- Open cmd and type
psql -a -U [username] -p [port] -h [server]
- Type
\c [database]
to connect to the database - Type
\dt
or\d
to show all tables
Solution 19 - Postgresql
Using psql : \dt
Or:
SELECT c.relname AS Tables_in FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relkind = 'r'
AND relname NOT LIKE 'pg_%'
ORDER BY 1
Solution 20 - Postgresql
This SQL Query works with most of the versions of PostgreSQL and fairly simple .
select table_name from information_schema.tables where table_schema='public' ;
Solution 21 - Postgresql
First of all you have to connect with your database like
my database is ubuntu
use this command to connect
\c ubuntu
This message will show
> "You are now connected to database "ubuntu" as user "postgres"."
Now
Run this command to show all tables in it
\d+
Solution 22 - Postgresql
as a quick oneliner
# just list all the postgres tables sorted in the terminal
db='my_db_name'
clear;psql -d $db -t -c '\dt'|cut -c 11-|perl -ne 's/^([a-z_0-9]*)( )(.*)/$1/; print'
or if you prefer much clearer json output multi-liner :
IFS='' read -r -d '' sql_code <<"EOF_CODE"
select array_to_json(array_agg(row_to_json(t))) from (
SELECT table_catalog,table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name ) t
EOF_CODE
psql -d postgres -t -q -c "$sql_code"|jq
Solution 23 - Postgresql
-
In PostgreSQL command-line interface after login, type the following command to connect with the desired database.
\c [database_name]
Then you will see this message You are now connected to database "[database_name]"
-
Type the following command to list all the tables.
\dt
Solution 24 - Postgresql
To view foreign tables in psql, run \dE
Solution 25 - Postgresql
(MySQL) shows tables list for current database
show tables;
(PostGreSQL) shows tables list for current database
select * from pg_catalog.pg_tables where schemaname='public';
Solution 26 - Postgresql
First you can connect with your postgres database using the postgre.app on mac or using postico. Run the following command:
psql -h localhost -p port_number -d database_name -U user_name -W
then you enter your password, this should give access to your database