-- 取插入行的 id（应用层直接 RETURNING 更好）
INSERT INTO t (name) VALUES ('x') RETURNING id, created_at;

-- 按自然周分组统计（周一为界）
SELECT date_trunc('week', created_at) AS week, count(*)
FROM posts GROUP BY week ORDER BY week DESC;

-- ILIKE 不区分大小写；注意 % 要转义，参数化交给驱动
SELECT * FROM posts WHERE title ILIKE '%' || $1 || '%';

-- 多词任一命中：OR 展开或 ANY
SELECT * FROM posts WHERE content ILIKE ANY (ARRAY['%docker%', '%k8s%']);

-- jsonb 取键 + 条件
SELECT * FROM site_config WHERE value->>'theme' = 'dark';

-- 给 jsonb 对象加一个键
UPDATE site_config
SET value = jsonb_set(value, '{locale}', '"zh-CN"')
WHERE key = 'ui';

-- 看表大小排行
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 查正在跑的慢查询
SELECT pid, now() - query_start AS duration, left(query, 80)
FROM pg_stat_activity
WHERE state = 'active' ORDER BY duration DESC;

-- 安全地加列（幂等，可重复执行）
ALTER TABLE posts ADD COLUMN IF NOT EXISTS is_hidden boolean NOT NULL DEFAULT false;