Postgresql Common SQL statement summary

1. Query link number

SELECT sum(numbackends) FROM pg_stat_database;

2. Check the deadlock status

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

3. Delete deadlock process

SELECT pg_cancel_backend(__pid__);
SELECT pg_terminate_backend(__pid__);

4. Backup the database

# sql filepg_dump dangerousdb &gt; 
# tar filepg_dump -U postgres -F c dangerousdb &gt; 
# gz filepg_dump -U postgres dangerousdb | gzip &gt;

5. Restore the database

# The database already existspg_restore -U postgres -Ft -d dbcooper &lt; 
# Create a new databasepg_restore -U postgres -Ft -C -d dbcooper &lt; 
#

6. Insert data

Insert a single piece of data

INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
)

Insert multiple pieces of data

INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
values(
column_1,
column_2,
column_3
),(
column_1,
column_2,
column_3
)...

Insert data queried from one table to another table

INSERT INTO TABLE_1
(
column_1,
column_2,
column_3
)
SELECT
column_1,
column_2,
column_3
FROM
TABLE_2
where TABLE_2condition;

7. Query the size of a single table in pg (excluding index)

select
    pg_size_pretty(pg_relation_size('schema.table_name'));

8. Query the size of all tables in the database

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

9. View the index in order

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

10. Query the size of the database

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

11. Query the locked table

select
    pg_class.relname as table,
    pg_database.datname as database,
    pid,
    mode,
    granted
from
    pg_locks,
    pg_class,
    pg_database
where
    pg_locks.relation = pg_class.oid
    and pg_locks.database = pg_database.oid;

12. Query the total size of all tables under a schema (unit MB, including index and data)

select
    schemaname ,
    round(sum(pg_total_relation_size(schemaname || '.' || tablename))/ 1024 / 1024) "Size_MB"
from
    pg_tables
where
    schemaname = '<schemaname>'
group by
    1;

13. Query the size of all tables and sort them (including indexes)

select
    table_schema || '.' || table_name as table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) as size
from
    information_schema.tables
order by
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc
limit 20;

14. The query table size is sorted by size and separate data and index

select
    table_name,
    pg_size_pretty(table_size) as table_size,
    pg_size_pretty(indexes_size) as indexes_size,
    pg_size_pretty(total_size) as total_size
from
    (
    select
        table_name,
        pg_table_size(table_name) as table_size,
        pg_indexes_size(table_name) as indexes_size,
        pg_total_relation_size(table_name) as total_size
    from
        (
        select
            ('"' || table_schema || '"."' || table_name || '"') as table_name
        from
            information_schema.tables) as all_tables
    order by
        total_size desc) as pretty_sizes;

select
    table_name,
    pg_size_pretty(table_size) as table_size,
    pg_size_pretty(indexes_size) as indexes_size,
    pg_size_pretty(total_size) as total_size
from
    (
    select
        table_name,
        pg_table_size(table_name) as table_size,
        pg_indexes_size(table_name) as indexes_size,
        pg_total_relation_size(table_name) as total_size
    from
        (
        select
            ('' || table_schema || '.' || table_name || '') as table_name
        from
            information_schema.tables) as all_tables
    order by
        total_size desc) as pretty_sizes;

This is the end of this article about postgresql’s commonly used SQL statement summary. For more related postgresql’s commonly used SQL content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!