List tables in a PostgreSQL schema

Question

When I do a  dt in psql I only get a listing of tables in the current schema  public by default    How can I get a list of all tables in all schemas or a particular schema

User · Answer

You can select the tables from information schema  SELECT   FROM information schema tables  WHERE table schema    public

User · Answer

For those coming across this in the future   If you would like to see a list of relations for several schemas    psql mydatabase mydatabase   SET search path TO public  usa     schema examples SET mydatabase    dt               List of relations  Schema        Name         Type     Owner -------- ----------------- ------- ----------  public   counties          table   postgres  public   spatial ref sys   table   postgres  public   states            table   postgres  public   us cities         table   postgres  usa      census2010        table   postgres

User · Answer

In all schemas     gt   dt       In a particular schema     gt   dt public     It is possible to use regular expressions with some restrictions   dt  public s   s t         List of relations  Schema   Name   Type    Owner  -------- ------ ------- -------  public   s      table   cpn  public   t      table   cpn  s        t      table   cpn      Advanced users can use regular-expression notations such as character classes  for example  0-9  to match any digit  All regular expression special characters work as specified in Section 9 7 3  except for   which is taken as a separator as mentioned above    which is translated to the regular-expression notation       which is translated to    and   which is matched literally  You can emulate these pattern characters at need by writing   for     R    for R   or  R   for R     is not needed as a regular-expression character since the pattern must match the whole name  unlike the usual interpretation of regular expressions  in other words    is automatically appended to your pattern   Write   at the beginning and or end if you don t wish the pattern to be anchored  Note that within double quotes  all regular expression special characters lose their special meanings and are matched literally  Also  the regular expression special characters are matched literally in operator name patterns  i e   the argument of  do

User · Answer

Alternatively to information schema it is possible to use pg tables   select   from pg tables where schemaname  public

[postgresql] List tables in a PostgreSQL schema

Examples related to postgresql

Examples related to postgresql-9.1

Examples related to psql