[]使用 pg_input_is_valid 验证 Postgres 中半结构化数据加载的数据类型

PostgreSQL培训考试认证中心 2025-03-11

处理大数据负载和/或数据类型更改可能很棘手 - 尤其是在大型数据集中查找和纠正个别错误。Postgres 版本 16、17 及更新版本具有一项新功能来帮助进行数据验证：pg_input_is_valid。

pg_input_is_valid是一个可以查询的 SQL 函数，它将确定给定的输入是否可以解析为特定类型，如数字、日期、JSON 等。这是一个非常基本的查询，用于询问“123”是否是有效的整数。

SELECT pg_input_is_valid('123', 'integer');
 pg_input_is_valid
-------------------
 t复制

此函数给出 t-真和 f-假的响应。因此，如果我问，SELECT pg_input_is_valid('123', 'date');答案将是“f”，因为这不是日期。

这不需要特殊的错误处理或特殊的脚本，它直接内置在 Postgres 中，可以与标准 SQL 一起使用。在 Crunchy Data，我们看到了一些很好的用例，您可以在导入数据之前验证数据。通常，如果使用暂存表或临时表，并且在运行最终数据复制或导入之前完成验证并识别有问题的行，则这种方法效果最好。今天让我们通过几个例子来了解验证输入功能如何提供帮助。

验证列更改的数据

数据库管理员经常需要更改数据类型。您可以轻松检查文本到整数等内容。您可能希望使用较新的 JSON 功能并摆脱旧格式。要将列移动到 JSON，pg_input_is_valid可以查询现有行以查看它们是否符合 JSONB。

SELECT pg_input_is_valid(data_column, 'jsonb')
FROM bytea_table;复制

您可能还想使用 pg_input_is_valid 检查要用于整数或日期的文本列。您可以为此使用常规有效性检查，也可以创建一个仅包含有效数据的新日期列。

UPDATE test_data
SET
    actual_date = CASE
        WHEN pg_input_is_valid (maybe_date, 'date') THEN maybe_date::date
        ELSE NULL
    END;

SELECT * from test_data ;

   name    | maybe_date   | actual_date
-----------+--------------+-------------
 David     | 2023-01-02   | 2023-01-02
 Elizabeth | Jan 1, 2024  |
复制

验证数据加载数据

假设您有一个包含客户数据的 CSV 文件，需要将其导入名为的表中customers。导入之前，最好确保 CSV 文件中的数据符合预期格式，尤其是age和signup_date列。

该表的结构如下：

customer_id SERIAL PRIMARY KEY,
name TEXT,
email TEXT,
age INTEGER,
signup_date DATE复制

创建临时表

将 CSV 数据导入暂存表，无需进行数据类型转换。所有内容都将以文本形式输入：

CREATE TEMP TABLE staging_customers (
    customer_id TEXT,
    name TEXT,
    email TEXT,
    age TEXT,
    signup_date TEXT
);

-- copy in the data to the temp table
COPY staging_customers FROM '/path/to/customers.csv' CSV HEADER;复制

排除无效行并将数据复制到最终表中

一旦确定了有问题的行，就可以手动修复或删除这些行。有时，更干净的选项是在pg_input_is_valid将数据复制到表中时跳过坏行并仅插入有效行。

INSERT INTO customers (name, email, age, signup_date)
SELECT name, email, age::integer, signup_date::date
FROM staging_customers
WHERE pg_input_is_valid(age, 'integer')
  AND pg_input_is_valid(signup_date, 'date');复制

pg_input_is_valid是 Postgres 工具包数据操作的一个很棒的最新补充 - 移动数据或更改数据类型。一般来说，我认为 pg_input_is valid 的最佳用途是使用暂存表进行两步数据导入、检查错误的验证步骤以及最终的数据迁移。由于这是内置在 Postgres 本身中的，因此无论您处理的是小型数据集还是数百万行，pg_input_is_valid它都是一种可扩展、高性能且可靠的数据清理和验证方法。

postgre