PostgreSQL: \dt

From wikieduonline
Jump to navigation Jump to search

List tables. https://www.postgresql.org/docs/current/app-psql.html

Examples

List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)

\dt *.* [1]
\dt
Did not find any relations
\dt

                           List of relations
 Schema |               Name                | Type  |       Owner
--------+-----------------------------------+-------+-------------------
 public | yourtable1                            | table | rds_master
 public | yourtable2                            | table | rds_master
 public | yourtable3                            | table | other_user
 public | yourtable4                            | table | rds_master


List all foreign tables [2]
\dE[S+]

Example Terraform RDS module

completePostgresql=> \dt *.*
                           List of relations
       Schema       |          Name           |    Type     |  Owner
--------------------+-------------------------+-------------+----------
 information_schema | sql_features            | table       | rdsadmin
 information_schema | sql_implementation_info | table       | rdsadmin
 information_schema | sql_parts               | table       | rdsadmin
 information_schema | sql_sizing              | table       | rdsadmin
 pg_catalog         | pg_aggregate            | table       | rdsadmin
 pg_catalog         | pg_am                   | table       | rdsadmin
 pg_catalog         | pg_amop                 | table       | rdsadmin
 pg_catalog         | pg_amproc               | table       | rdsadmin
 pg_catalog         | pg_attrdef              | table       | rdsadmin
 pg_catalog         | pg_attribute            | table       | rdsadmin
 pg_catalog         | pg_auth_members         | table       | rdsadmin
 pg_catalog         | pg_authid               | table       | rdsadmin
 pg_catalog         | pg_cast                 | table       | rdsadmin
 pg_catalog         | pg_class                | table       | rdsadmin
 pg_catalog         | pg_collation            | table       | rdsadmin
 pg_catalog         | pg_constraint           | table       | rdsadmin
 pg_catalog         | pg_conversion           | table       | rdsadmin
 pg_catalog         | pg_database             | table       | rdsadmin
 pg_catalog         | pg_db_role_setting      | table       | rdsadmin
 pg_catalog         | pg_default_acl          | table       | rdsadmin
 pg_catalog         | pg_depend               | table       | rdsadmin
 pg_catalog         | pg_description          | table       | rdsadmin
 pg_catalog         | pg_enum                 | table       | rdsadmin
 pg_catalog         | pg_event_trigger        | table       | rdsadmin
 pg_catalog         | pg_extension            | table       | rdsadmin
 pg_catalog         | pg_foreign_data_wrapper | table       | rdsadmin
 pg_catalog         | pg_foreign_server       | table       | rdsadmin
 pg_catalog         | pg_foreign_table        | table       | rdsadmin
 pg_catalog         | pg_index                | table       | rdsadmin
 pg_catalog         | pg_inherits             | table       | rdsadmin
 pg_catalog         | pg_init_privs           | table       | rdsadmin
 pg_catalog         | pg_language             | table       | rdsadmin
 pg_catalog         | pg_largeobject          | table       | rdsadmin
 pg_catalog         | pg_largeobject_metadata | table       | rdsadmin
 pg_catalog         | pg_namespace            | table       | rdsadmin
 pg_catalog         | pg_opclass              | table       | rdsadmin
 pg_catalog         | pg_operator             | table       | rdsadmin
 pg_catalog         | pg_opfamily             | table       | rdsadmin
 pg_catalog         | pg_partitioned_table    | table       | rdsadmin
 pg_catalog         | pg_policy               | table       | rdsadmin
 pg_catalog         | pg_proc                 | table       | rdsadmin
 pg_catalog         | pg_publication          | table       | rdsadmin
 pg_catalog         | pg_publication_rel      | table       | rdsadmin
 pg_catalog         | pg_range                | table       | rdsadmin
 pg_catalog         | pg_replication_origin   | table       | rdsadmin
 pg_catalog         | pg_rewrite              | table       | rdsadmin
 pg_catalog         | pg_seclabel             | table       | rdsadmin
 pg_catalog         | pg_sequence             | table       | rdsadmin
 pg_catalog         | pg_shdepend             | table       | rdsadmin
 pg_catalog         | pg_shdescription        | table       | rdsadmin
 pg_catalog         | pg_shseclabel           | table       | rdsadmin
 pg_catalog         | pg_statistic            | table       | rdsadmin
 pg_catalog         | pg_statistic_ext        | table       | rdsadmin
 pg_catalog         | pg_statistic_ext_data   | table       | rdsadmin
 pg_catalog         | pg_subscription         | table       | rdsadmin
 pg_catalog         | pg_subscription_rel     | table       | rdsadmin
 pg_catalog         | pg_tablespace           | table       | rdsadmin
 pg_catalog         | pg_transform            | table       | rdsadmin
 pg_catalog         | pg_trigger              | table       | rdsadmin
 pg_catalog         | pg_ts_config            | table       | rdsadmin
 pg_catalog         | pg_ts_config_map        | table       | rdsadmin
 pg_catalog         | pg_ts_dict              | table       | rdsadmin
 pg_catalog         | pg_ts_parser            | table       | rdsadmin
 pg_catalog         | pg_ts_template          | table       | rdsadmin
 pg_catalog         | pg_type                 | table       | rdsadmin
 pg_catalog         | pg_user_mapping         | table       | rdsadmin
 pg_toast           | pg_toast_1213           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1247           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1255           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1260           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1262           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1417           | TOAST table | rdsadmin
 pg_toast           | pg_toast_1418           | TOAST table | rdsadmin
 pg_toast           | pg_toast_14490          | TOAST table | rdsadmin
 pg_toast           | pg_toast_14495          | TOAST table | rdsadmin
 pg_toast           | pg_toast_14500          | TOAST table | rdsadmin
 pg_toast           | pg_toast_14505          | TOAST table | rdsadmin
 pg_toast           | pg_toast_2328           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2396           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2600           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2604           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2606           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2609           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2612           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2615           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2618           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2619           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2620           | TOAST table | rdsadmin
 pg_toast           | pg_toast_2964           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3079           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3118           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3256           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3350           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3381           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3394           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3429           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3456           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3466           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3592           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3596           | TOAST table | rdsadmin
 pg_toast           | pg_toast_3600           | TOAST table | rdsadmin
 pg_toast           | pg_toast_6000           | TOAST table | rdsadmin
 pg_toast           | pg_toast_6100           | TOAST table | rdsadmin
 pg_toast           | pg_toast_826            | TOAST table | rdsadmin
( 104 rows)

Errors

Error: permission denied for table your-table

Solution

\dt *.*

Table size

SELECT
 schema_name,
 relname,
 pg_size_pretty(table_size) AS size,
 table_size

FROM (
      SELECT
        pg_catalog.pg_namespace.nspname           AS schema_name,
        relname,
        pg_relation_size(pg_catalog.pg_class.oid) AS table_size

      FROM pg_catalog.pg_class
        JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
    ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

Related queries

Related

See also

  • https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
  • https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
  • https://tableplus.com/blog/2018/07/postgresql-how-to-list-all-tables-excluding-views.html
  • https://tableplus.com/blog/2018/04/postgresql-how-to-list-all-tables.html
  • Advertising: