Psql list all tables

Question

I would like to list all tables in the liferay database in my PostgreSQL install  How do I do that   I would like to execute SELECT   FROM applications  in the liferay database  applications is a table in my liferay db  How is this done     Here s a list of all my databases   postgres    list                               List of databases Name       Owner     Encoding     Collate        Ctype        Access privileges     ----------- ---------- ---------- ------------- ------------- -----------------------  liferay     postgres   UTF8       en GB UTF-8   en GB UTF-8    Tc postgres                                                                          postgres CTc postgres                                                                 liferay CTc postgres  lportal     postgres   UTF8       en GB UTF-8   en GB UTF-8     postgres    postgres   UTF8       en GB UTF-8   en GB UTF-8     template0   postgres   UTF8       en GB UTF-8   en GB UTF-8    c postgres                                                                           postgres CTc postgres  template1   postgres   UTF8       en GB UTF-8   en GB UTF-8    c postgres                                                                           postgres CTc postgres  5 rows   postgres

User · Answer

Connect to the database  then list the tables    c liferay  dt   That s how I do it anyway   You can combine those two commands onto a single line  if you prefer    c liferay  dt

User · Answer

If you wish to list all tables  you must use    dt       to indicate that you want all tables in all schemas  This will include tables in pg catalog  the system tables  and those in information schema  There s no built-in way to say  all tables in all user-defined schemas   you can  however  set your search path to a list of all schemas of interest before running  dt   You may want to do this programmatically  in which case psql backslash-commands won t do the job  This is where the INFORMATION SCHEMA comes to the rescue  To list tables   SELECT table name FROM information schema tables WHERE table schema    public     BTW  if you ever want to see what psql is doing in response to a backslash command  run psql with the -E flag  eg     psql -E regress     regress    list           QUERY            SELECT d datname as  Name          pg catalog pg get userbyid d datdba  as  Owner          pg catalog pg encoding to char d encoding  as  Encoding          d datcollate as  Collate          d datctype as  Ctype          pg catalog array to string d datacl  E  n   AS  Access privileges  FROM pg catalog pg database d ORDER BY 1                               so you can see that psql is searching pg catalog pg database when it gets a list of databases  Similarly  for tables within a given database   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  WHEN  f  THEN  foreign table  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    It s preferable to use the SQL-standard  portable INFORMATION SCHEMA instead of the Pg system catalogs where possible  but sometimes you need Pg-specific information  In those cases it s fine to query the system catalogs directly  and psql -E can be a helpful guide for how to do so

User · Answer

A one-line example is   dt schemaname      in your senario   dt public

User · Answer

To see the public tables you can do  list tables   dt   list table  view  and access privileges   dp or  z   or just the table names   select table name from information schema tables where table schema    public

User · Answer

In SQL Query  you can write this code   select table name from information schema tables where table schema  YOUR TABLE SCHEME     Replace your table scheme with YOUR TABLE SCHEME   Example    select table name from information schema tables where table schema  eLearningProject     To see all scheme and all tables  there is no need of where clause   select table name from information schema tables

User · Answer

This can be used in automation scripts if you don t need all tables in all schemas     for table in   psql -qAntc   dt    cut -d   -f2   do             done

[postgresql] Psql list all tables

Examples related to postgresql

Examples related to psql