如何将JSON数据插入到PostgreSQL数据库表中

Question

我是一个初学者，正在尝试使用教程将JSON值插入到数据库中。

我使用以下命令创建了表格：

CREATE TABLE table_name( id character varying(50),
                         data json NOT NULL,
                         active boolean NOT NULL,
                         created_at timestamp with time zone NOT NULL,
                         updated_at timestamp with time zone NOT NULL,
                         CONSTRAINT table_name_pkey PRIMARY KEY (id)
                       );

表格是通过 table_name 创建的。

现在我正在尝试将值插入到数据库中：

INSERT INTO table_name
SELECT id,data,active,created_at,updated_at
FROM json_populate_record (NULL::table_name,
     '{
        "id": "1",
        "data":{
                "key":"value"
                },
         "active":true,
         "created_at": SELECT NOW(),
         "updated_at": SELECT NOW()
       }'
  );

出现如下错误：

错误：JSON类型的输入语法无效 '{

请问有谁可以帮助我解决并将JSON值插入到数据库中吗？

- DRV

---

为什么 id 是一个 varchar，而你显然在使用整数值？ - user330315

Answer 1

您不能在 JSON 字符串中包含任意 SQL 命令。从 JSON 的“角度”来看，SELECT NOW() 是一个无效的值，因为它缺少双引号。但即使您使用了 "select now()"，它也会被执行为 SQL 查询并替换为当前时间戳。

但我不明白为什么您要将其包装到 jsonb_populate_record 中。更好的解决方案（至少在我看来）是：

INSERT INTO table_name (id, data, active, created_at, updated_dat)
VALUES ('1', '{"key": "value"}', true, now(), now());

如果你真的想让事情变得复杂，你需要使用字符串拼接：

SELECT id,data,active,created_at,updated_at
FROM json_populate_record (NULL::table_name,
     format('{
        "id": "1",
        "data":{
                "key":"value"
                },
         "active":true,
         "created_at": "%s", 
         "updated_at": "%s"
       }', now(), now())::json
  );

- user330315

---

第一段代码适用于我，但请将ID值更改为1而不是“1”@a_horse_with_no_name - DRV

你的id列被定义为varchar，因此你需要将它作为一个字符串传递。在JSON值中，字符串写作"1"，而在SQL中，字符串写作'1'。 - user330315

请编辑您的 SQL 查询，关闭 VALUES ('1',.......);；您错过了终止符 )。 - Sumax