PostgreSQL UPSERT (INSERT ... ON CONFLICT UPDATE) fails

Question

我的postgresql数据库中有一行要更新。

=> 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
=> 

我的代码生成了这个SQL语句，以插入其他内容。更新一个已更改的字段：

=> 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
=> 

我不明白的是，我可以选择这一行，但它显示的是一个非NULL的num_syncs。但是UPSERT失败了，因为它没有(重新(设置num_syncs(值4不变(。

有人看到我缺了什么吗？

Fwiw，表格定义如下：

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 1

首先检查NOT NULL约束。这是有道理的，因为他们必须满足INSERT才能成功。

如果您确信已经存在匹配的行，请使用常规的UPDATE。

一种替代方案可能是使用CHECK (colname IS NOT NULL)约束而不是NOT NULL。