PostgreSQL小技巧

在字符串中添加换行符

在PostgreSQL中如果想在字符串中添加特殊符号，是不等直接在字符串中显示的，如：select '\n'，最后查出来的就是\n而不是换行符。

这是需要借用E。在PostgreSQL中，字符串前面的 E 表示该字符串是一个转义字符串（escape string）。转义字符串可以包含用于表示特殊字符或控制字符的转义序列。

select E'hello \n world'
-------
?column?
hello 
 world

元数据管理——查询表的元数据信息

select
    t1.ordinal_position, -- 字段排序号
    t1.relkind, -- 表类型。v视图，r实体表，p分区表
    t1.table_schema, -- 模式名
    split_part(t1.description, '。', 1) as table_name, -- 表名
    t1.table_name as table_code, -- 表代码
    t1.description,    -- 表描述
    split_part(t1.comment, '。', 1) as column_name, -- 字段代码
    t1.column_name as column_code, -- 字段名
    regexp_replace(t1.comment, '\s', '', 'g'), -- 字段描述 -- 将换行等字段替换掉
    t1.data_type, -- 字段数据类型
    t1.column_length, -- 字段长度
    case
            when contype = 'p' then '是'
            else '否'
    end is_primary -- 是否主键
from (
    SELECT
        col.table_schema AS table_schema, -- 模式名
        col.table_name, -- 表名
        obj_description(c.oid) AS description,    -- 表描述
        col.column_name, -- 字段名
        col_description(c.oid, col.ordinal_position) AS comment, -- 字段描述
        case
            when col.udt_name = 'varchar' then concat(col.udt_name, '(', col.character_maximum_length, ')')
            when col.udt_name = 'timestamp' then concat(col.udt_name, '(', col.datetime_precision, ')')
            when col.udt_name = 'date' then concat(col.udt_name, '(', col.datetime_precision, ')')
            when col.udt_name = 'numeric' then concat(col.udt_name, '(', col.numeric_precision, ',', col.numeric_scale, ')')
            when col.udt_name like 'int%' then concat('numeric', '(', col.numeric_precision, ',', col.numeric_scale, ')')
            when col.udt_name like 'float%' then concat('numeric', '(16,4)')
            when col.udt_name = 'text' then 'varchar(2000)'
            else col.udt_name
        end as data_type, -- 字段数据类型
        col.ordinal_position, -- 字段排序号
        col.data_type AS col_type, -- 字段类型。如字符串character varying
        col.udt_name, -- 字段类型。如字符串varchar
        col.is_nullable, -- 字段是否可空
        case
            when col.udt_name = 'varchar' then concat(col.character_maximum_length)
            when col.udt_name = 'timestamp' then concat(col.datetime_precision)
            when col.udt_name = 'date' then concat(col.datetime_precision)
            when col.udt_name = 'numeric' or col.udt_name like 'int%' then concat(col.numeric_precision, ',', col.numeric_scale)
            when col.udt_name like 'int%' then concat(col.numeric_precision, ',', col.numeric_scale)
            when col.udt_name like 'float%' then concat(col.numeric_precision, ',', col.numeric_scale)
            when col.udt_name = 'text' then '2000'
            else concat(col.numeric_precision, ',', col.numeric_scale)
        end as column_length, -- 字段数据类型
        col.character_maximum_length, -- 字符串类型的长度
        col.numeric_precision, col.numeric_scale,  -- 数字类型的长度精度
        col.datetime_precision,  -- 日期类型精度
        c.relkind -- 表类型。v视图，r实体表，p分区表
    FROM information_schema.columns AS col
    LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema
    LEFT JOIN pg_class c ON col.table_name = c.relname AND c.relnamespace = ns.oid
) t1
left join (
    SELECT
        ns.nspname as table_schema, -- 模式名
        t.relname AS table_name, -- 表名
        c.conname, -- 约束名称
        c.contype, -- 约束类型。p:primary key；
        unnest(c.conkey) as ordinal_position -- 字段排序号
    FROM pg_constraint c
    LEFT JOIN pg_namespace ns ON (c.connamespace = ns.oid)
    LEFT JOIN pg_class t ON (c.conrelid = t.oid)
) t2
    on (t2.table_schema = t1.table_schema and -- 模式名
            t2.table_name = t1.table_name and -- 表名
            t2.ordinal_position = t1.ordinal_position -- 字段顺序号
        )
WHERE t1.table_schema = 'data'
    AND t1.table_name in ('wahaha') -- 指定生成表名
ORDER BY t1.table_schema, t1.table_name, t1.ordinal_position
;

元数据管理——逆向生成建表语句

我有一张表，表里有各表的一些基本属性，具体表结构如下：

-- "data".table_mode definition

-- Drop table

-- DROP TABLE "data".table_mode;

CREATE TABLE "data".table_mode (
	table_name varchar(255) NULL, -- 表代码名（建表时写错了，table_code应该会更好，或者另外加一个字段，这个字段就写中文名）
	field_name varchar(255) NULL,
	field_code varchar(255) NULL,
	"comment" varchar(255) NULL, -- 表注释
	field_type varchar(50) NULL,
	is_key varchar(2) NULL,
	is_foreign_key varchar(2) NULL,
	is_not_null varchar(2) NULL
);

通过上面的信息，生成里面的表结构DDL

WITH table_info AS (
    SELECT
        table_name,
        field_name,
        field_code,
        field_type,
        is_not_null,
        comment
    FROM
        table_mode
),
create_table_statements AS (
    SELECT
        table_name,
        string_agg(
            format(E'\t%s %s %s, -- %s ', field_code, field_type,
                   CASE WHEN is_not_null='是' THEN 'NOT NULL' ELSE 'NULL' END, comment) , E' \n') AS columns_info
    FROM
        table_info
    GROUP BY
        table_name
),
column_comments AS (
    SELECT
        ti.table_name,
        string_agg(
            format('COMMENT ON COLUMN "data".%s.%s IS %L;', ti.table_name, ti.field_name, ti.comment),
            E'\n') AS column_comments
    FROM
        table_mode ti
    GROUP BY
        ti.table_name
)
SELECT
    format(E'CREATE TABLE "data".%s (\n', tcs.table_name) || tcs.columns_info || E'\n);' AS create_table_statement,
    cc.column_comments
FROM
    create_table_statements tcs
JOIN
    column_comments cc ON tcs.table_name = cc.table_name;

写的不太好，注意建表语句最后一个字段的，号