Difference between revisions of "PostgreSQL: \dt"
Jump to navigation
Jump to search
↑ 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
(→Errors) |
|||
| (30 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 |
| + | == 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 | List of relations | ||
| − | Schema | Name | Type | Owner | + | [[Schema]] | Name | Type | Owner |
--------+-----------------------------------+-------+------------------- | --------+-----------------------------------+-------+------------------- | ||
| − | public | yourtable1 | table | [[rds_master]] | + | [[public]] | yourtable1 | table | [[rds_master]] |
public | yourtable2 | table | rds_master | public | yourtable2 | table | rds_master | ||
public | yourtable3 | table | other_user | public | yourtable3 | table | other_user | ||
| Line 13: | Line 19: | ||
| − | |||
| − | |||
| − | |||
List all [[foreign tables]] <ref>https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546</ref> | List all [[foreign tables]] <ref>https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546</ref> | ||
\dE[S+] | \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 == | == Related queries == | ||
| Line 26: | Line 151: | ||
== Related == | == Related == | ||
| − | * | + | * List [[tables]] and [[privileges]]: <code>[[\dp]]</code> |
== See also == | == See also == | ||
| + | * {{Table}} | ||
* {{psql}} | * {{psql}} | ||
| − | |||
[[Category:SQL]] | [[Category:SQL]] | ||
Latest revision as of 07:06, 8 March 2023
List tables. https://www.postgresql.org/docs/current/app-psql.html
Contents
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]
SELECT tablename AS TABLE FROM pg_tables WHERE schemaname = 'public';[3]SELECT * FROM information_schema.tables;[4]
Related[edit]
- List tables and privileges:
\dp
See also[edit]
- Table, foreign table, external table,
show table, Delete rows of a table, Table Partitioning,\dt - PostgreSQL:
psql,.psqlrc, .pgpass,$HOME/.pg service.conf,-U, -W, -d, meta-commands:\du, \dg, \ddp, \dp, \list, \dn, \dt, \c, \l, \di, \q,show users,show,su - postgres,psql -c, \?, \connect,--version, --help, PL/pgSQL
Advertising:
