如何为英国历史英镑-先令-便士系统选择PostgreSQL数据类型

为什么MONEY类型不适用？ #

PostgreSQL的MONEY类型依赖数据库的区域设置（lc_monetary），默认情况下可能未配置为英镑格式，导致无法识别£符号。更关键的是，MONEY是为十进制现代货币设计的，无法直接适配£sd这种1英镑=20先令、1先令=12便士的旧制体系，因此不适合存储和计算这类历史货币数据。

解析字符串转统一数值是最优方案 #

要实现求和、与无量纲数相乘这类计算，必须将£sd字符串转换为可计算的统一数值类型。推荐转换为便士总数（用BIGINT存储），完全避免浮点精度损失，同时计算逻辑清晰高效。

具体实现步骤 #

1. 创建解析函数：将£sd字符串转为便士数 #

编写PL/pgSQL函数处理不同格式的输入（纯英镑、英镑+先令、完整的£sd格式）：

CREATE OR REPLACE FUNCTION parse_gsd_to_pence(gsd_str TEXT)
RETURNS BIGINT AS $$
DECLARE
    pounds NUMERIC := 0;
    shillings NUMERIC := 0;
    pence NUMERIC := 0;
    cleaned_str TEXT;
BEGIN
    IF gsd_str IS NULL OR gsd_str = '' THEN
        RETURN NULL;
    END IF;

    -- 清理字符串：移除£符号并去除首尾空格
    cleaned_str := TRIM(REPLACE(gsd_str, '£', ''));

    -- 提取英镑部分（匹配开头的数字）
    IF cleaned_str ~ '^\d+' THEN
        pounds := (regexp_match(cleaned_str, '^\d+'))[1]::NUMERIC;
        cleaned_str := TRIM(regexp_replace(cleaned_str, '^\d+', '', 'g'));
    END IF;

    -- 提取先令部分（匹配带s的数字）
    IF cleaned_str ~ '\d+s' THEN
        shillings := (regexp_match(cleaned_str, '(\d+)s'))[1]::NUMERIC;
        cleaned_str := TRIM(regexp_replace(cleaned_str, '\d+s', '', 'g'));
    END IF;

    -- 提取便士部分（匹配带d的数字）
    IF cleaned_str ~ '\d+d' THEN
        pence := (regexp_match(cleaned_str, '(\d+)d'))[1]::NUMERIC;
        cleaned_str := TRIM(regexp_replace(cleaned_str, '\d+d', '', 'g'));
    END IF;

    -- 校验剩余内容，若有未解析部分则抛出错误
    IF cleaned_str != '' THEN
        RAISE EXCEPTION 'Invalid £sd format: %', gsd_str;
    END IF;

    -- 转换为总便士数：1英镑=240便士，1先令=12便士
    RETURN (pounds * 240 + shillings * 12 + pence)::BIGINT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 创建存储表并导入数据 #

用BIGINT存储便士数，可选保留原始字符串用于溯源：

CREATE TABLE historical_prices (
    id SERIAL PRIMARY KEY,
    price_pence BIGINT NOT NULL,
    original_gsd TEXT -- 保留原始输入字符串
);

-- 导入示例数据
INSERT INTO historical_prices (price_pence, original_gsd)
VALUES 
(parse_gsd_to_pence('£57 19s 7d'), '£57 19s 7d'),
(parse_gsd_to_pence('£8 1s'), '£8 1s'),
(parse_gsd_to_pence('£30'), '£30');

3. 创建转换函数：将便士数转回£sd格式 #

计算完成后，可将结果转回传统£sd格式展示：

CREATE OR REPLACE FUNCTION pence_to_gsd(pence BIGINT)
RETURNS TEXT AS $$
DECLARE
    pounds INTEGER;
    shillings INTEGER;
    remaining_pence INTEGER;
    parts TEXT[] := '{}';
BEGIN
    pounds := pence / 240;
    remaining_pence := pence % 240;
    shillings := remaining_pence / 12;
    remaining_pence := remaining_pence % 12;

    -- 构建结果片段，仅保留非零部分
    IF pounds > 0 THEN
        parts := array_append(parts, '£' || pounds);
    END IF;
    IF shillings > 0 THEN
        parts := array_append(parts, shillings || 's');
    END IF;
    IF remaining_pence > 0 THEN
        parts := array_append(parts, remaining_pence || 'd');
    END IF;

    -- 拼接片段，返回最终字符串
    RETURN array_to_string(parts, ' ');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

常用计算示例 #

• 求和并转回£sd格式：

SELECT 
    SUM(price_pence) AS total_pence,
    pence_to_gsd(SUM(price_pence)) AS total_gsd
FROM historical_prices;

• 与无量纲数相乘：

SELECT 
    price_pence * 2 AS multiplied_pence,
    pence_to_gsd(price_pence * 2) AS multiplied_gsd
FROM historical_prices WHERE id = 1;

额外建议 #

• 始终保留原始£sd字符串：便于数据溯源和验证解析结果。
• 避免使用浮点类型：用整数存储便士数完全消除精度损失，比FLOAT或NUMERIC更高效可靠。
• 扩展函数健壮性：可根据实际输入格式调整正则表达式，比如处理带逗号的英镑数值（如£1,234）。

内容的提问来源于stack exchange，提问作者Michael Gastner