如何将JavaScript数组轻松转换为Postgres BigInt数组

直接SQL转换存储 #

假设你有一张存储用户数据的表，结构定义如下：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    convert_ids BIGINT[]
);

插入数据时，直接对传入的JSON字符串的convert字段做类型转换即可：

INSERT INTO users (name, convert_ids)
VALUES (
    'John Doe',
    '{"name" : "John Doe","convert" : [32197,4234523,4235423]}'::json->>'convert'::bigint[]
);

如果表中已有JSON类型的字段（比如data JSON），需要更新convert_ids列时，执行以下语句：

UPDATE users
SET convert_ids = (data->>'convert')::bigint[]
WHERE id = 1;

在PL/pgSQL函数中处理 #

如果是通过自定义函数接收JSON参数并存储，写法如下：

CREATE OR REPLACE FUNCTION insert_user(p_user JSON)
RETURNS VOID AS $$
BEGIN
    INSERT INTO users (name, convert_ids)
    VALUES (
        p_user->>'name',
        (p_user->>'convert')::bigint[]
    );
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT insert_user('{"name" : "John Doe","convert" : [32197,4234523,4235423]}');

关键说明 #

PostgreSQL会自动处理JSON数组的格式转换——把JSON的方括号数组（[32197,4234523,4235423]）转换成SQL数组的大括号格式（{32197,4234523,4235423}），无需手动替换字符。如果使用JSONB类型，只需把语句中的::json换成::jsonb，逻辑完全一致。

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