查看单个表大小

方法一:查询单个表

1
select pg_size_pretty(pg_relation_size('table_name'));

方法二:查出所有表并按大小排序

1
2
3
4
5
6
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

方法三:查出所有表按大小排序并分离 data 与 index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

参考: CSDN 博客

创建用户和数据库

安装 PostgreSQL 并创建数据库:

1
2
3
4
psql -U postgres -h 127.0.0.1

create role channel with login password 'channel123$' valid until 'infinity';
create database channel with encoding='UTF8' owner=channel connection limit=-1;

参考: PostgreSQL 安装配置

导入导出数据

参考: PostgreSQL Backup and Restore

从 SELECT 插入数据

1
2
3
insert into npc_list (op_bind, uid, day)
select op_bind, uid, first_date
from npc_white_list

参考: SQL INSERT INTO SELECT

获取 UTC 时间

1
update npc_list set update_time = timezone('utc', now())

PostgreSQL 的进程列表

查找和终止挂起的查询:

1
2
3
4
5
6
7
8
-- 查看所有活动进程
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- 取消查询
SELECT pg_cancel_backend(PID);

-- 强制终止
SELECT pg_terminate_backend(PID);

CROSS JOIN 示例

1
2
3
4
5
6
7
select
    attribute->>'uid' as uid,
    attribute->>'day' as day,
    (building->>'lv')::integer * (building->>'num')::integer
FROM public.user_daily_report
CROSS JOIN jsonb_array_elements(metric->'building') as building
WHERE attribute->>'day' = '2019-09-01'

SQLAlchemy 相关

CastingArray

1
2
3
class CastingArray(ARRAY):
    def bind_expression(self, bindvalue):
        return cast(bindvalue, self)

参考资源

获取当前月份

1
select date_part('month', now());