Difference between revisions of "PostgreSQL: \dt"

From wikieduonline
Jump to navigation Jump to search
 
(42 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
https://www.postgresql.org/docs/current/app-psql.html
+
List tables. https://www.postgresql.org/docs/current/app-psql.html
−
* [[PostgreSQL]]: <code>[[\dt]]</code>
 
  
 +
== Examples ==
 +
List tables from all [[schemas]] (if *.* is omitted will only <code>[[show SEARCH_PATH]]</code> ones)
 +
\dt *.* <ref>https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546</ref>
 +
 +
\dt
 +
[[create table|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]] <ref>https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546</ref>
 +
\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 ==
 +
<code>[[Error: permission denied for table]] your-table</code>
 +
 +
=== Solution ===
 +
<code> \dt *.*</code>
 +
 +
== [[Table size]] ==
 +
 +
{{table size}}
 +
 +
== Related queries ==
 +
 +
* <code>[[SELECT tablename AS TABLE FROM pg_tables WHERE schemaname = 'public';]]</code> <ref>https://tableplus.com/blog/2018/07/postgresql-how-to-list-all-tables-excluding-views.html</ref>
 +
* <code>[[SELECT * FROM information_schema.tables;]]</code> <ref>https://tableplus.com/blog/2018/04/postgresql-how-to-list-all-tables.html</ref>
  
 
== Related ==
 
== Related ==
−
* {{TOC operation databases}}
+
* List [[tables]] and [[privileges]]: <code>[[\dp]]</code>
  
 
== See also ==
 
== See also ==
 +
* {{Table}}
 
* {{psql}}
 
* {{psql}}
−
* {{SQL}}
 
  
 
[[Category:SQL]]
 
[[Category:SQL]]

Latest revision as of 07:06, 8 March 2023

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

Examples[edit]

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[edit]

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[edit]

Error: permission denied for table your-table

Solution[edit]

\dt *.*

Table size[edit]

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[edit]

Related[edit]

See also[edit]

  • ↑ 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
    • Retrieved from "https://www.wikieduonline.com/index.php?title=PostgreSQL:_%5Cdt&oldid=230856"

    Advertising: