PostgreSQL: Show tables in PostgreSQL

Asked 2023-09-20 20:31:04 View 544,565

What's the equivalent to show tables (from MySQL) in PostgreSQL?

Answers

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.

Answered   2023-09-20 20:31:04

  • @StephenCorwin No, \l is the equivalent of show databases in MySQL. dtshow tables and lshow databases - anyone
  • \dt is very useful. That pg_catalog.pg_tables one is much less so, as it appears to lump internal tables together with the user-created ones for whatever database you happen to be connected to. - anyone
  • psql my_db_name should be run in order \dt to work. When I ran psql without a database name, I got a "No relations found" message - anyone
  • Without system tables: SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' - anyone
  • You first need \c <DATABASE_NAME> to choose your database. - anyone

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.

Answered   2023-09-20 20:31:04

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:

  1. \? list all the commands
  2. \l list databases
  3. \conninfo display information about current connection
  4. \c [DBNAME] connect to new database, e.g., \c template1
  5. \dt list tables of the public schema
  6. \dt <schema-name>.* list tables of certain schema, e.g., \dt public.*
  7. \dt *.* list tables of all schemas
  8. Then you can run SQL statements, e.g., SELECT * FROM my_table;(Note: a statement must be terminated with semicolon ;)
  9. \q quit psql

Answered   2023-09-20 20:31:04

  • "psql -U postgres" will log and connect to "postgres" database - anyone
  • \d+ Details about the table. \x Displays the output in an expanded way. (Retype \x to turn the expanded display off) - anyone
  • @sina \du has nothing to do with listing tables, which is what the question is asking about. There are many more psql commands not listed here. - anyone
  • Ah appending ; to the query worked! Thanks. - anyone

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

Answered   2023-09-20 20:31:04

  • +1 although for completeness, mysql show tables only shows the current schema, its good to think of it this way, mysql only has one database but multiple schemas, where postgresql can have mutliple databases (catalogs) and schemas. So the equiv should be table_schema='DB_NAME'; - anyone
  • Not exactly standard sql, cannot use "||" to concatenate strings on mssql - anyone
  • @ChRoNoN: that is standard SQL . || has been the string concatenation operator in the SQL standard since 1983 - it's MS SQL that uses a non-standard string concatenation operator. - anyone
  1. First login as postgres user:

    sudo su - postgres

  2. connect to the required db: psql -d databaseName

  3. \dt would return the list of all table in the database you're connected to.

Answered   2023-09-20 20:31:04

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.

Answered   2023-09-20 20:31:04

  • This is a nice quick starter fir someone not familiar with pgsql - anyone

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

Answered   2023-09-20 20:31:04

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

Answered   2023-09-20 20:31:04

use only see a tables

=> \dt

if want to see schema tables

=>\dt+

if you want to see specific schema tables

=>\dt schema_name.* 

Answered   2023-09-20 20:31:04

  • I'm pretty sure you're confusing + with S. The latter (the letter) shows schema tables. The + simply shows extra information. - anyone

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

Answered   2023-09-20 20:31:04

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

Answered   2023-09-20 20:31:04

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

Answered   2023-09-20 20:31:04

  • I'm not sure this is answering the question. I think the OP is (was) trying to know all the tables in his database, not get all the rows from a particular table in his database... right? - anyone
select 
  * 
from 
  pg_catalog.pg_tables 
where 
  schemaname != 'information_schema' 
  and schemaname != 'pg_catalog';

Answered   2023-09-20 20:31:04

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

Answered   2023-09-20 20:31:04

Those steps worked for me with PostgreSQL 13.3 and Windows 10

  1. Open cmd and type psql -a -U [username] -p [port] -h [server]
  2. Type \c [database] to connect to the database
  3. Type \dt or \d to show all tables

Answered   2023-09-20 20:31:04

\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.

Answered   2023-09-20 20:31:04

\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.

Answered   2023-09-20 20:31:04

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.

Answered   2023-09-20 20:31:04

  • command="\d+" psql_command="psql --no-password -d "ch_api_db" -U "ch_api_user" -c "${command}"" docker exec -it "${container_name}" sh -c "${psql_command}" gives me an error about syntax error near d - anyone

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

Answered   2023-09-20 20:31:04

  • Don't post duplicate answers. - anyone
  • it isn't duplicate there is small change this directly gives the table name , i tired to edit the original answer but it wasn't approved hence gave an answer which works - anyone
  • The answer by Milen A. Radev provides the table_name. The answer by Reynante Daitol contains the rest of this code. If you believe that this code offers something new and unique that is a reason to include an explanation that points that out. Without the explanation people are left guessing why it is different or potentially better. - anyone

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.

Answered   2023-09-20 20:31:04

  1. 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]"

  1. Type the following command to list all the tables.

        \dt
    

Answered   2023-09-20 20:31:04

shows tables list for current database

SELECT * FROM pg_catalog.pg_tables;

Answered   2023-09-20 20:31:04


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

Answered   2023-09-20 20:31:04

as a "quick oneliner"

# how-to list all the tables 
export PGUSER='postgres'
export PGHOST='postgres-host-end-point'
export PGPORT=5432
export PGDATABASE=foobar

PGPASSWORD='uber-secret' psql -d $PGDATABASE -t -q -c \
 "SELECT table_catalog,table_schema,table_name 
   FROM information_schema.tables where table_schema='public';

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

Answered   2023-09-20 20:31:04

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+

Answered   2023-09-20 20:31:04

\dt will work. And the equivalence of it is

SELECT
  n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' 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', 'p', '')
  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;

Answered   2023-09-20 20:31:04

To view foreign tables in psql, run \dE

Answered   2023-09-20 20:31:04

In Postgres, a quick and easy way to show tables is by using the follwoing command.

    /dt

This is a meta-command that is exclusive to the psql interactive terminal for PostgreSQL. A list of all tables in the current database, along with their schema names and table kinds will be shown when you perform the psql command dt.

Moreover, To retrieve a list of the tables in the current database, execute the SQL query below:

    SELECT table
    FROM schema.tables
    WHERE table_schema= 'public';

Hope, this is helpful. Thank you.

Answered   2023-09-20 20:31:04

This will show all tables in the schema. Run this line of code.

\dt

Answered   2023-09-20 20:31:04

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

Answered   2023-09-20 20:31:04