PostgreSQL Show tables in PostgreSQL

Question

What s the equivalent to show tables  from MySQL  in PostgreSQL

User · Answer

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:-

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.

User · Answer

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  lt schema-name gt    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

User · Answer

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

User · Answer

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  quot  database name  quot   Type the following command to list all the tables       dt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The most straightforward way to list all tables at command line is  for my taste    psql -a -U  lt user gt  -p  lt port gt  -h  lt server gt  -c   dt    For a given database just add the database name    psql -a -U  lt user gt  -p  lt port gt  -h  lt server gt  -c   dt   lt database name gt    It works on both Linux and Windows

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To view foreign tables in psql  run  dE

User · Answer

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

User · Answer

use only see a tables     gt   dt   if want to see schema tables    gt  dt    if you want to see specific schema tables    gt  dt schema name

User · Answer

select       from    pg catalog pg tables  where    schemaname     information schema     and schemaname     pg catalog

User · Answer

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

User · Answer

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  lt  gt   pg catalog      AND n nspname  lt  gt   information schema      AND n nspname      pg toast  AND pg catalog pg table is visible c oid  ORDER BY 1 2

User · Answer

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  lt  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

[postgresql] PostgreSQL: Show tables in PostgreSQL

Examples related to postgresql