postgresql - Trying to perform a UPSERT on a selected column

Question

This is my table definition

                             Table "sample"
        Column        |           Type           | Collation | Nullable | Default
----------------------+--------------------------+-----------+----------+---------
 identifier           | character varying(255)   |           | not null |<---- Primary key
 payload              | jsonb                    |           | not null |
 last_updated         | timestamp with time zone |           | not null | now()
 full_payload         | jsonb                    |           | not null |

And, I am trying to do an UPSERT on one of the column

INSERT INTO sample AS t (identifier,full_payload) 
VALUES ('12345','{"a":"b"}'::JSONB) 
ON CONFLICT ON CONSTRAINT sample_pkey 
DO UPDATE SET full_payload=EXCLUDED.full_payload,payload=t.payload,last_updated=t.last_updated;

And, I am getting below error. Why am I not able to access the original value for column payload? Any suggestion?

ERROR: null value in column "payload" of relation "sample" violates not-null constraint
DETAIL: Failing row contains (12345, null, 2021-01-21 20:16:34.388903+00, {"a": "b"}).

I am using Postgres version 11.10

question from:https://stackoverflow.com/questions/65835241/trying-to-perform-a-upsert-on-a-selected-column

Answer 1

Your INSERT only provides a value for the full_payload column, but not the payload column. However the payload column is defined as not null so the INSERT (not the UPDATE) fails because of that. You have to provide a non-null value for the column payload in your INSERT statement.

You can also remove the unnecessary updates to the current value in the UPDATE part, they serve no purpose. I would rather expect that last_updated should be set to the current timestamp.

So something like:

INSERT INTO sample AS t (identifier, full_payload, payload) 
VALUES ('12345','{"a":"b"}', '{}') 
ON CONFLICT ON CONSTRAINT sample_pkey 
DO UPDATE 
  SET full_payload = EXCLUDED.full_payload, 
      last_updated = current_timestamp;