Home Show tables in PostgreSQL

# Show tables in PostgreSQL

flybywire
1#
flybywire Published in 2009-04-20 19:07:39Z
 What's the alternative to show tables (from MySQL) in PostgreSQL?
rogerdpack
2#
 From the psql command line interface, 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.
3#
 (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'); 
JLarky
4#
 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.
bsb
5#
 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; ************************** 
J4cK
6#
 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
nish
7#
 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.
JohnK
8#
 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.
Community
9#
 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
user4642897
10#
 \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.
lauri108
11#
 \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.
Aryan
12#
 use only see a tables => \dt if want to see schema tables =>\dt+ if you want to see specific schema tables =>\dt schema_name.*
Reynante Daitol
13#
Reynante Daitol Reply to 2017-06-05 02:18:02Z
 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'; 
Patcho
14#
 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
Abdelhak Ohammou
15#
Abdelhak Ohammou Reply to 2017-06-25 17:41:14Z
 select * from pg_catalog.pg_tables where schemaname != 'information_schema' and schemaname != 'pg_catalog'; 
Code Lღver
16#
Code Lღver Reply to 2017-07-19 11:19:34Z
 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+ 
Yuci
17#
 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 \q quit psql
 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: 1) Connect to the desired database: psql mydb  2) 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