如何在PostgreSQL 9.6中存储含长度/精度的列类型信息？

嘿，我刚好碰到过类似的需求，regtype确实坑在只存类型OID，丢了长度、精度这些关键修饰符。用TEXT又要自己做验证，太麻烦了。给你分享个既保证类型合法性，又能完整保留类型参数的方案：

核心思路 #

PostgreSQL里类型的修饰符（比如VARCHAR的长度、NUMERIC的精度/刻度）是存在typmod（类型修饰符整数）里的。我们可以用两个字段分别存储：

• base_type regtype：存基础类型（自动保证类型合法性，关联系统表）
• type_modifier INTEGER：存类型的修饰符值

然后通过系统函数format_type把这两个值拼接成带参数的完整类型字符串。

具体实现步骤 #

1. 创建存储表 #

先建一个表，用regtype和integer字段分别存基础类型和修饰符：

CREATE TABLE foo (
    name TEXT PRIMARY KEY,
    base_type regtype NOT NULL,
    type_modifier INTEGER
);

2. 插入数据（带解析辅助函数） #

为了方便插入带修饰符的类型，我们可以写个小函数来自动拆分类型字符串，提取基础类型和typmod：

CREATE OR REPLACE FUNCTION parse_typedesc(p_type TEXT)
RETURNS TABLE(base_type regtype, typmod INTEGER) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        -- 去掉括号及内容，得到基础类型
        (regexp_replace(p_type, '\(.*\)', ''))::regtype,
        CASE 
            WHEN p_type LIKE '%(%)' THEN
                CASE (regexp_replace(p_type, '\(.*\)', ''))::regtype
                    -- 处理字符类型（VARCHAR/CHAR等）
                    WHEN 'character varying'::regtype, 'varchar'::regtype, 'char'::regtype THEN
                        (split_part(split_part(p_type, '(', 2), ')', 1))::integer
                    -- 处理数值类型（NUMERIC/DECIMAL）
                    WHEN 'numeric'::regtype, 'decimal'::regtype THEN
                        -- NUMERIC的typmod是高16位存精度，低16位存刻度
                        (split_part(split_part(p_type, '(', 2), ',', 1)::integer << 16) 
                        | split_part(split_part(p_type, '(', 2), ')', 1)::integer
                    -- 其他类型如果有修饰符，可以在这里扩展
                    ELSE NULL
                END
            ELSE NULL
        END;
END;
$$ LANGUAGE plpgsql STABLE;

现在插入数据就很方便了：

-- 无修饰符的类型
INSERT INTO foo(name, base_type, type_modifier)
SELECT 'my_field_1', base_type, typmod FROM parse_typedesc('character varying');

-- 带长度的VARCHAR
INSERT INTO foo(name, base_type, type_modifier)
SELECT 'my_field_2', base_type, typmod FROM parse_typedesc('VARCHAR(50)');

-- 带精度刻度的NUMERIC
INSERT INTO foo(name, base_type, type_modifier)
SELECT 'my_field_3', base_type, typmod FROM parse_typedesc('NUMERIC(32,16)');

3. 查询完整类型 #

用format_type函数把基础类型和修饰符拼接成你想要的带参数的类型字符串：

SELECT 
    name,
    format_type(base_type::oid, type_modifier) AS sql_type
FROM foo;

预期查询结果：

4. （可选）创建视图简化查询 #

如果不想每次都写format_type，可以建个视图封装查询逻辑：

CREATE VIEW foo_full_types AS
SELECT 
    name,
    format_type(base_type::oid, type_modifier) AS sql_type
FROM foo;

之后直接查视图就行：

SELECT * FROM foo_full_types;

为什么这个方案比TEXT好？ #

• 自动验证类型合法性：regtype字段只接受PostgreSQL支持的有效类型，不会存无效的类型字符串，省去了自定义CHECK约束的麻烦
• 保留参照完整性：regtype关联到系统表pg_type，类型名称变更时（比如重命名类型），存储的regtype会自动同步
• 灵活还原类型字符串：通过format_type可以精准还原带修饰符的类型，不用自己拼接字符串容易出错

内容的提问来源于stack exchange，提问作者Sebastian Widz