json - 将 JSON 文件导入我的 postgreSQL DB - INSERT 的表达式比目标列多

Question

基于这个答案：

所以回答

我想将 JSON 文件导入我的 postgreSQL 数据库。

但我收到以下错误：

错误：INSERT 的表达式多于目标列

CREATE SEQUENCE app_user_id_seq
      INCREMENT 1
      START 0
      MINVALUE 0
      MAXVALUE 2147483647
      CACHE 1;

CREATE TABLE "app_user" (
  "a_u_id" bigint NOT NULL DEFAULT nextval('app_user_id_seq'::regclass),
  "auth_0_user_id" text PRIMARY KEY NOT NULL 
);

ALTER TABLE ONLY app_user
    ADD COLUMN password_hash text,
    ADD COLUMN given_name text,
    ADD COLUMN family_name text,
    ADD COLUMN email_verified boolean,
    ADD COLUMN email text,
    ADD COLUMN connection text,
    ADD COLUMN tenant text,
    ADD COLUMN nickname text,
    ADD COLUMN name text,
    ADD COLUMN picture text,
    ADD COLUMN created_at text,
    ADD COLUMN updated_at text,
    ADD COLUMN weight text,
    ADD COLUMN opt_in boolean,
    ADD COLUMN skill_level text,
    ADD COLUMN imperial boolean,
    ADD COLUMN height text,
    ADD COLUMN bar boolean;
    ALTER TABLE ONLY app_user
    RENAME COLUMN auth_0_user_id TO user_id;

with customer_json (doc) as (
   values 
  ('[{
    "user_id": "a",
    "given_name": "a",
    "family_name": "a",
    "nickname": "a",
    "name": "a",
    "email": "a",
    "email_verified": true,
    "picture": "a.png",
    "connection": "a",
    "created_at": "22",
    "updated_at": "22",
    "weight": "0",
    "opt_in": false,
    "skill_level": "a",
    "imperial": false,
    "height": "0",
    "bar": false,
    "password_hash": "a",
    "tenant": "a"
  },
  {
    "user_id": "a",
    "given_name": "a",
    "family_name": "a",
    "nickname": "a",
    "name": "a",
    "email": "a",
    "email_verified": true,
    "picture": "a.png",
    "connection": "a",
    "created_at": "22",
    "updated_at": "22",
    "weight": "0",
    "opt_in": false,
    "skill_level": "a",
    "imperial": false,
    "height": "0",
    "bar": false,
    "password_hash": "a",
    "tenant": "a"
  }]'::json)
)
insert into app_user (user_id, given_name, family_name, nickname, name, email, email_verified, picture, connection, created_at, updated_at, weight, opt_in, skill_level, imperial, height, bar, password_hash, tenant)
select p.*
from customer_json l
  cross join lateral json_populate_recordset(null::app_user, doc) as p
on conflict (user_id) do update
  set given_name = excluded.given_name, 
  family_name = excluded.family_name,
  nickname = excluded.nickname, 
  email = excluded.email,
  name = excluded.name, 
  email_verified = excluded.email_verified,
  picture = excluded.picture, 
  connection = excluded.connection,
  created_at = excluded.created_at, 
  updated_at = excluded.updated_at,
  weight = excluded.weight,
  opt_in = excluded.opt_in,
  skill_level = excluded.skill_level,
  imperial = excluded.imperial,
  height = excluded.height,
  bar = excluded.bar,
  password_hash = excluded.password_hash,
  tenant = excluded.tenant;

我确信该表还有 19 列，如 INSERT 表达式加上一个 app_user_id_seq（序列），在插入新行时应该自动生成。

知道我做错了什么吗？谢谢！

标签： jsonpostgresql