postgresqlcommandpsqlpostgresql-9.1dbtable

How to list the tables of schemas in PostgreSQL?


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?


Solution

  • In all schemas:

    => \dt *.*
    

    In a particular schema:

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