pgsql常用命令

登录

psql -h hostname -d dbname -U username

成功连接后，你将看到类似以下的提示符，表示你已经连接到数据库：
dbname=>

列出所有库

\l

切换数据库

\c your_database_name

列出所有表

\dt

列出当前数据库中的所有表
\dt schema_name.*

查询所有数据库存储大小

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;

k可以快速查看
\l+

查看表空间的占用情况, 列出所有数据库的大小

SELECT
    pg_database.datname AS database_name,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM
    pg_database
ORDER BY
    pg_database_size(pg_database.datname) DESC;

列出当前所有表及占用空间

查询将列出当前数据库中所有用户表的模式、名称以及占用的空间大小，并按照表占用的空间大小降序排列

SELECT
    table_schema,
    table_name,
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_table_size
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
ORDER BY
    pg_total_relation_size(table_schema || '.' || table_name) DESC;

查找最大的表

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_table_size
FROM
    information_schema.tables
ORDER BY
    pg_total_relation_size(table_schema || '.' || table_name) DESC
LIMIT 1;

删除表数据

# 删除表
DELETE FROM table_name;

# 删除数据库中的数据
TRUNCATE TABLE your_table_name;