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
| Line 139: | Line 139: | ||
=== Solution === | === Solution === | ||
| − | + | <code> \dt *.*</code> | |
== [[Table size]] == | == [[Table size]] == | ||
Revision as of 07:06, 8 March 2023
List tables. https://www.postgresql.org/docs/current/app-psql.html
Contents
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
SELECT tablename AS TABLE FROM pg_tables WHERE schemaname = 'public';[3]SELECT * FROM information_schema.tables;[4]
Related
- List tables and privileges:
\dp
See also
- Table, foreign table, external table,
show table, Delete rows of a table, Table Partitioning,\dt, Hash table - 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, \conninfo,--version, --help, PL/pgSQL
Advertising:
