How to view all tables in PostgreSQL database

View all tables in PostgreSQL database

As a person who has worked in operation and maintenance, perhaps for most operation and maintenance personnel, they cannot rely on management tools to operate the database, such as pgAdmin, often log in to a certain database server remotely and query tables in the database using the command line.

Get all table information in the current db.

select * from pg_tables;

If the user-defined table is not processed, it will be placed under a schema named public by default.

select tablename from pg_tables where schemaname='public'

postgres=# select tablename from pg_tables where schemaname='public';
 tablename
-----------
 tb_test
(1 Line records)

To further view the table structure, you can use the \d table name

postgres=# \d tb_test;
                Data table "public.tb_test"
 Column |      type      | Collation | Nullable | Default
------+----------------+-----------+----------+---------
 id   | integer        |           |          |
 name | character(100) |           |          |

And if you are not doing CRUD in large quantities, operating directly on the command line is also a time to exercise your syntax level.

postgres=# insert into tb_test(id,name) values(1,'huangbaokang');
INSERT 0 1
postgres=# select * from tb_test;
 id |                                                 name

----+-----------------------------------------------------------------
---------------------------
  1 | huangbaokang

(1 Line records)

PostgreSQL database tablespace, index, table size query

Postgresql database

1: Query the table space size

select pg_size_pretty(pg_tablespace_size('pg_default'));

Two: Query all database sizes

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_databas;

Three: Query the specified index size

select pg_size_pretty(pg_relation_size('Index field name'));

Four: Query all index sizes of the specified table

select pg_size_pretty(pg_indexes_size('Table name'));

5: Query all index sizes in the specified mode, sorted from large to small

select relname,indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='Mode Name' order by pg_relation_size(relid) desc;

Six: Query the specified table size (only specify the table data)

select pg_size_pretty(pg_relation_size('Table name'));

7: Query the total size of the specified table (including table data and index)

select pg_size_pretty(pg_total_relation_size('Table name'));

8: Query all table sizes in the specified mode (only specify table data, from large to small)

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='Mode Name' order by pg_relation_size(relid) desc;

Nine: Query the data size of all tables in the specified schema, total index size, total size, and number of rows

SELECT
    table_size.relname Table name,
    pg_size_pretty ( pg_relation_size ( relid ) ) Table data size,
    pg_size_pretty ( pg_indexes_size ( relid ) ) Total index size of table,
    pg_size_pretty ( pg_total_relation_size ( relid ) ) Total table size,
    Number of rows 
FROM
pg_stat_user_tables table_size
    LEFT JOIN (
        SELECT
            relname,
            reltuples :: DECIMAL ( 19, 0 ) Number of rows 
        FROM
        pg_class r
        JOIN pg_namespace n ON ( relnamespace =  ) 
        WHERE
            relkind = 'r' 
            AND  = 'Mode Name' 
        ) table_num ON table_num.relname = table_size.relname 
WHERE
    schemaname = 'Mode Name' 
ORDER BY
    pg_relation_size ( relid ) DESC;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.