postgresql - INSERT 具有比加载 JSON 对象的表达式更多的目标列

Question

我正在尝试运行一个简单的插入脚本，该脚本会将 json 对象加载到 postgresql 数据库中。我不断收到以下错误消息：

No data found
ERROR:  INSERT has more target columns than expressions
LINE 132: INSERT INTO books (title, author, genre, year, createdAt, up...
                                                         ^
SQL state: 42601
Character: 4152


CREATE TABLE Books (id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, genre VARCHAR(255), year INTEGER, createdAt timestamp with time zone NOT NULL, updatedAt  TIMESTAMP WITH TIME ZONE NOT NULL)

WITH books_json (doc) AS (VALUES(
'[
    {
        "author": "Jane Austen",
        "createdAt": "2018-10-04 18:21:59.011 +00:00",
        "genre": "Classic",
        "title": "Emma",
        "updatedAt": "2018-10-04 18:21:59.011 +00:00",
        "year": 1815
    }
]'::json))
INSERT INTO books (title, author, genre, year, createdAt, updatedAt)
SELECT p.* FROM books_json l CROSS JOIN lateral
json_populate_recordset(NULL::contacts, doc) AS p ON conflict (id)
do UPDATE SET name = excluded.name, active = excluded.active;

标签： postgresql

Answer 1

我就是这样做的。我首先将数组扩展为行（在这种情况下只有一行），然后取消引用并强制转换（如有必要）每个键。

WITH books_json (doc) AS (VALUES(
'[
    {
        "author": "Jane Austen",
        "createdAt": "2018-10-04 18:21:59.011 +00:00",
        "genre": "Classic",
        "title": "Emma",
        "updatedAt": "2018-10-04 18:21:59.011 +00:00",
        "year": 1815
    }
]'::json)
), expand as (
  select json_array_elements(doc) as doc
   from books_json
)
insert into books (title, author, genre, year, createdat, updatedat)
select doc->>'title' as title,
       doc->>'author' as author,
       doc->>'genre' as genre,
       (doc->>'year')::int as year,
       (doc->>'createdAt')::timestamptz as createdat,
       (doc->>'updatedAt')::timestamptz as updatedat
  from expand;