Skip to content
Advertisement

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)

=>

Advertisement

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.

1 People found this is helpful
Advertisement