I have a row in my postgresql database that I want to update. My code generates this SQL statement to insert else update the one field that's changed: What I don't understand is that I can select that one row and it is present with a non-NULL num_syncs. But the UPSERT is failing because it doesn't (re)set num_syncs (value 4

PostgreSQL UPSERT (INSERT … ON CONFLICT UPDATE) fails

I have a row in my postgresql database that I want to update.

=> SELECT * FROM reading_group_reading_session
WHERE group_id = 439 
  AND group_type = 'focus_group' 
  AND reg_user_id = 28056 
  AND device_id = ''
  AND reading_date = '2021-06-03';
  id   | group_id | group_type  | reg_user_id | device_id | reading_date | seconds_reading | num_syncs 
-------+----------+-------------+-------------+-----------+--------------+-----------------+-----------
 35532 |      439 | focus_group |       28056 |           | 2021-06-03   |            3310 |         4
(1 row)

Time: 1.820 ms
=>

My code generates this SQL statement to insert else update the one field that’s changed:

=> INSERT INTO
[more] - >   INSERT INTO reading_group_reading_session
  (group_id,group_type,reg_user_id,device_id,reading_date,seconds_reading)
VALUES
  (439,'focus_group',28056,'','2021-06-03',3320)
ON CONFLICT
  (group_id, group_type, reg_user_id, device_id, reading_date)
DO UPDATE SET
    seconds_reading = 3320;
ERROR:  23502: null value in column "num_syncs" violates not-null constraint
DETAIL:  Failing row contains (115399, 439, focus_group, 28056, , 2021-06-03, 3320, null).
SCHEMA NAME:  public
TABLE NAME:  reading_group_reading_session
COLUMN NAME:  num_syncs
LOCATION:  ExecConstraints, execMain.c:1700
Time: 3.017 ms
=>

What I don’t understand is that I can select that one row and it is present with a non-NULL num_syncs. But the UPSERT is failing because it doesn’t (re)set num_syncs (value 4 unchanged).

Anyone see what I’m missing?

Fwiw, the table definition is this:

                                  Table "public.reading_group_reading_session"
     Column      |      Type       |                                 Modifiers                                  
-----------------+-----------------+----------------------------------------------------------------------------
 id              | integer         | not null default nextval('reading_group_reading_session_id_seq'::regclass)
 group_id        | integer         | not null
 group_type      | group_type_name | 
 reg_user_id     | integer         | not null
 device_id       | text            | 
 reading_date    | date            | 
 seconds_reading | integer         | not null
 num_syncs       | integer         | not null
Indexes:
    "reading_group_reading_session_pkey" PRIMARY KEY, btree (id)
    "reading_group_reading_session_idx_dgid" UNIQUE, btree (group_id, group_type, reg_user_id, device_id, reading_date)
Check constraints:
    "reading_group_reading_session_group_id_check" CHECK (group_id > 0)
    "reading_group_reading_session_minutes_reading_check" CHECK (seconds_reading >= 0)
    "reading_group_reading_session_num_syncs_check" CHECK (num_syncs >= 0)
    "reading_group_reading_session_reg_user_id_check" CHECK (reg_user_id >= 0)

=>

Answer

The NOT NULL constraints are checked first. That makes sense, because they must be satisfied for an INSERT to succeed.

If you know for sure that there is already a matching row, use a regular UPDATE.

An alternative might be to use a CHECK (colname IS NOT NULL) constraint instead of NOT NULL.

Advertisement

Answer