Postgres Upsert Requires All NOT NULL values to be included in the object.

[nickreese]

Been fighting an issue today where I couldn't do a simple upsert of an object looked like {id: 123, value: "myvalue"}

The primary key of the table is id but the import would fail with a "null value in column "required_value" violates not-null constraint" error.

As soon as I include the two required values, everything runs smoothly.

Digging into the script.js for postgres, I believe it is because of how the upsert logic works.

INSERT INTO public.mytable (
    "id",
    "required_value",
    "myvalue",
    ... long list of columns
  )
VALUES
  (
    123,
    DEFAULT
    "insertedvalue",
    ... long list of DEFAULT
  ) ON CONFLICT ("id") DO
UPDATE
SET
  "id" = 6,

Because "required_value" is NOT NULL and there is no default, the query fails. No idea how to fix it as SQL isn't my strength, but this appears to be a bug.