2/12/2024 0 Comments Postgres show tablesoid, reltuples, relname, relnamespace, reltablespace reltablespace, pg_class. oid )) AS index_bytes, SUM ( pg_total_relation_size ( reltoastrelid )) AS toast_bytes FROM ( SELECT pg_class. oid )) AS total_bytes, SUM ( pg_indexes_size ( c. reltuples ) AS row_estimate, SUM ( pg_total_relation_size ( c. inhrelid ), pg_inherit_short AS ( SELECT * FROM pg_inherit WHERE inhparent NOT IN ( SELECT inhrelid FROM pg_inherit )) SELECT parent :: regclass, coalesce ( spcname, 'default' ) pg_tablespace_name, row_estimate, pg_size_pretty ( total_bytes ) AS total, pg_size_pretty ( index_bytes ) AS INDEX, pg_size_pretty ( toast_bytes ) AS toast, pg_size_pretty ( table_bytes ) AS TABLE, 100 * total_bytes :: float8 / sum ( total_bytes ) OVER () AS PERCENT FROM ( SELECT *, total_bytes - index_bytes - COALESCE ( toast_bytes, 0 ) AS table_bytes FROM ( SELECT parent, reltablespace, SUM ( c. inhparent FROM pg_inherit child, pg_inherits parent WHERE child. WITH RECURSIVE pg_inherit ( inhrelid, inhparent ) AS ( select inhrelid, inhparent FROM pg_inherits UNION SELECT child. relnamespace ) a WHERE oid = parent ) a ORDER BY total_bytes DESC General Table Size Information Grouped For Partitioned Tables oid ) parent FROM pg_class LEFT JOIN pg_inherit_short ON inhrelid = oid WHERE relkind IN ( 'r', 'p' ) ) c LEFT JOIN pg_namespace n ON n. reltoastrelid, COALESCE ( inhparent, pg_class. oid, reltuples, relname, relnamespace, pg_class. oid )) OVER ( partition BY parent ) AS index_bytes, SUM ( pg_total_relation_size ( reltoastrelid )) OVER ( partition BY parent ) AS toast_bytes, parent FROM ( SELECT pg_class. oid )) OVER ( partition BY parent ) AS total_bytes, SUM ( pg_indexes_size ( c. reltuples ) OVER ( partition BY parent ) AS row_estimate, SUM ( pg_total_relation_size ( c. oid, nspname AS table_schema, relname AS TABLE_NAME, SUM ( c. inhrelid ), pg_inherit_short AS ( SELECT * FROM pg_inherit WHERE inhparent NOT IN ( SELECT inhrelid FROM pg_inherit )) SELECT table_schema, TABLE_NAME, row_estimate, pg_size_pretty ( total_bytes ) AS total, pg_size_pretty ( index_bytes ) AS INDEX, pg_size_pretty ( toast_bytes ) AS toast, pg_size_pretty ( table_bytes ) AS TABLE, total_bytes :: float8 / sum ( total_bytes ) OVER () AS total_size_share FROM ( SELECT *, total_bytes - index_bytes - COALESCE ( toast_bytes, 0 ) AS table_bytes FROM ( SELECT c. In this article, we explored three methods: using the psql command-line tool, querying the information_schema and creating ER diagram using Luna Modeler.WITH RECURSIVE pg_inherit ( inhrelid, inhparent ) AS ( select inhrelid, inhparent FROM pg_inherits UNION SELECT child. Retrieving a list of all database tables in PostgreSQL is essential for understanding the structure and contents of your database. Tip: you can split the diagram into sub-diagrams according to schemas, add notes and other information etc. Learn more about visualization of existing PostgreSQL databases. Just create a database connection:Īnd then click on Connect and load existing structure. The advantage is greater clarity, the ability to search, view details, view SQL scripts and identify dependencies.Ĭreating an ER diagram in Luna Modeler is easy. Using Luna Modeler to show tables in PostgresĪnother option is to display all tables of the selected database using an ER diagram.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |