PostgreSql 项目实战 9 - 常用sql

1 设置boolean数据类型

-- 1 查看当前字段类型
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'sys_role' 
AND column_name IN ( 'dept_check_strictly');

-- 2. 先删除默认值约束
ALTER TABLE sys_role ALTER COLUMN dept_check_strictly DROP DEFAULT;

-- 3 修改字段类型为 boolean
ALTER TABLE sys_role 
ALTER COLUMN dept_check_strictly TYPE boolean 
USING CASE WHEN dept_check_strictly = 1 THEN true ELSE false END;


-- 4. 重新设置默认值（如果需要）
ALTER TABLE sys_role ALTER COLUMN dept_check_strictly SET DEFAULT false;

2 查询重复字段

SELECT 
    invoice_id,
    COUNT(*) as duplicate_count
FROM verifac_invoice
GROUP BY invoice_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, invoice_id;