在字符串中添加換行符

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

這是需要借用E。在PostgreSQL中，字符串前面的 E 表示該字符串是一個轉(zhuǎn)義字符串（escape string）。轉(zhuǎn)義字符串可以包含用于表示特殊字符或控制字符的轉(zhuǎn)義序列。

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

元數(shù)據(jù)管理——查詢表的元數(shù)據(jù)信息

select
    t1.ordinal_position, -- 字段排序號
    t1.relkind, -- 表類型。v視圖，r實體表，p分區(qū)表
    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, -- 字段數(shù)據(jù)類型
    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, -- 字段數(shù)據(jù)類型
        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, -- 字段數(shù)據(jù)類型
        col.character_maximum_length, -- 字符串類型的長度
        col.numeric_precision, col.numeric_scale,  -- 數(shù)字類型的長度精度
        col.datetime_precision,  -- 日期類型精度
        c.relkind -- 表類型。v視圖，r實體表，p分區(qū)表
    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
;

元數(shù)據(jù)管理——逆向生成建表語句

我有一張表，表里有各表的一些基本屬性，具體表結(jié)構(gòu)如下：

-- "data".table_mode definition

-- Drop table

-- DROP TABLE "data".table_mode;

CREATE TABLE "data".table_mode (
	table_name varchar(255) NULL, -- 表代碼名（建表時寫錯了，table_code應(yīng)該會更好，或者另外加一個字段，這個字段就寫中文名）
	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
);

通過上面的信息，生成里面的表結(jié)構(gòu)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;

寫的不太好，注意建表語句最后一個字段的，號