ON CONFLICT (id) DO update SET 字段 = EXCLUDED.字段 和 ON CONFLICT (id) DO NOTHING 的用法

需求：单表数据，id冲突就按照id更新，id不冲突就新增插入。没有用mybatisplus的框架，自己手写动态sql进行单表插入或者更新。这就需要使用pgsql的 ON CONFLICT (id) DO update SET 字段 = EXCLUDED.字段; 注意 id 是做了唯一性约束的，才能使用 ON CONFLICT (id)

1、没有插入的字段保存在数据库表结果是null，插入字段值是空字符串''或者空格' '或者空格'    '保存结果也是空字符串''或者空格' '或者空格'    '

INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value, target_value_string, target_big)
VALUES(5, '测试保存1-4', 100, 1, 1, '测试', 100.000000, '100.000', 14.000000);

-- 没有插入的字段有 target_value_string, target_big，这两个没插入的字段保存在数据库表结果是null，
字段值是空字符串''或者空格' '或者空格'    '保存结果也是空字符串''或者空格' '或者空格'    '

INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value)
VALUES(101112, '', 100, NULL, NULL, ' ', 100.000000);

INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value)
VALUES(101113, '', 100, NULL, NULL, '            ', 100.000000);

select *from goods g where id in (101112,101113)

2、 ON CONFLICT (id) DO update SET name = "小迪"; 给name字段赋值 "小迪"

-- 不用EXCLUDED，id冲突时给name字段赋值更新语句的name值，其他没插入的字段的值不会改变
INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value)
VALUES(101112, '名称1', 100, NULL, NULL, ' ', 100.000000)
ON CONFLICT (id) DO update set name='id冲突给name字段赋值更新语句的name值';

3、ON CONFLICT (id) DO update SET name = EXCLUDED.name; 给字段name赋值就是插入的语句里name的值

-- 用EXCLUDED，id冲突时给name字段赋值插入语句的name值，其他没插入的字段的值不会改变
INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value)
VALUES(101112, 'id冲突给name字段赋值插入语句的name值', 100, NULL, NULL, ' ', 100.000000)
ON CONFLICT (id) DO update SET name = EXCLUDED.name;

4、ON CONFLICT (id) DO update SET name = EXCLUDED.name,remark = EXCLUDED.remark; 给多个字段赋值插入语句对应字段的值

-- 用EXCLUDED，id冲突时给name字段和remark字段赋值插入语句的name值和remark值，其他没插入的字段的值不会改变
INSERT INTO public.goods
(id, "name", "storage", goodstype, count, remark, target_value)
VALUES(101112, 'id冲突给name字段赋值插入语句的name值', 100, NULL, NULL, '备注1', 100.000000)
ON CONFLICT (id) DO update SET name = EXCLUDED.name,remark = EXCLUDED.remark;

5、ON CONFLICT (id) DO NOTHING，id有唯一性约束，id冲突了不进行插入，id不冲突就进行插入
/*
注意：
id字段有唯一性约束，才能用ON CONFLICT (id) DO NOTHING 才正确，
如果是插入数据时，对表t_lab_document_rule 的document_name_code 字段
用 ON CONFLICT (document_name_code) DO NOTHING;直接报语法错误
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
*/
insert
	into
	public.t_dms_check_template
(id,
	factory_code,
	template_code,
	template_name,
	area_id,
	check_period,
	check_frequency,
	warning_time,
	cron_expression,
	check_item_num,
	dimension,
	enable_status,
	data_status,
	add_user_id,
	add_time,
	edit_user_id,
	edit_time)
values(1798607270912794626,
'test_001',
'2024661',
'点检模板测试',
1792427891213418498,
'shift',
1,
20,
null,
1,
'machine',
-1,
'0',
'1694585670603333633',
1717656364551,
'1694585670603333633',
1717656364554) ON CONFLICT (id) DO NOTHING;

表 t_dms_check_template 有了 id=1798607270912794626 的数据，末尾用了ON CONFLICT (id) DO NOTHING;去插入是不会报错的，
并且不会插入此数据，表里不会有两条id=1798607270912794626 的数据，只有之前的那一条id=1798607270912794626 的数据。
在插入语句某些字段的值和已有id数据里对应字段的值不同，在插入时是不能更新这些字段的值的。
要更新字段值那就在插入后用update语句更新。如果插入数据ID和已有的不同，那就正常保存插入的数据。

刷脚本是一次性刷测试库、uat库和生产库的。如果只在测试库插入了数据，在uat和生产库没插入数据。那就在插入脚本后用
ON CONFLICT (id) DO NOTHING; 可以避免在测试库插入时id相同报错，在没插入此数据的uat库和生产库能正常插入。

6、使用场景
ON CONFLICT (id) DO update SET 字段 = EXCLUDED.字段;
id冲突做更新，id不冲突做新增，建议insert和update里的字段一样，但是update里可以不写创建时间字段。

6.1
-- id冲突，不更新update里没有的字段，只更新update里的字段，并且insert里没有的字段在数据库表里的值不会改变
-- update里没有 target_value_string 字段，所以其仍然是100，不是120；
-- insert里没插入goodstype字段，所以其仍然是101；因为只更新update里的字段，就算insert里插入了goodstype字段值为102，其仍然是101
-- name字段会更新为'泽741'，remark字段会更新为'泽不存在就插入741'
--使用场景：id冲突做更新操作，要不改变创建时间，那就在update里不加创建时间字段
INSERT INTO public.goods
(id, "name", "storage", count, remark, target_value, target_value_string, target_big)
VALUES(1012, '泽741', 100, NULL, '泽不存在就插入741', 100.000000, '120', 105)
ON CONFLICT (id) DO update SET name = EXCLUDED.name,remark = EXCLUDED.remark;

6.2
-- id不冲突，不执行update语句了，insert没有的字段的值会在数据库表里保存为null
-- 使用场景：insert和update里的字段是外部接口返回给本系统的字段，其他没返回的字段就保存为null
INSERT INTO public.goods
(id, "storage", goodstype, count, remark, target_value, target_value_string, target_big)
VALUES(101746, 100, NULL, NULL, '泽不存在就插入741', 100.000000, '100.000', 10.150000)
ON CONFLICT (id) DO update SET name = EXCLUDED.name,remark = EXCLUDED.remark;

6.3
-- id冲突，update里有name字段，但是insert没有name字段，name字段值就会更新为null
-- 使用场景 
INSERT INTO public.goods
(id, "storage", goodstype, count, remark, target_value, target_value_string, target_big)
VALUES(1012, 100, NULL, NULL, '泽不存在就插入74', 100.000000, '100.000', 10.150000)
ON CONFLICT (id) DO update SET name = EXCLUDED.name,remark = EXCLUDED.remark;

7、字段的非空限制（非null限制）
-- 做了非null限制的字段不能保存null值，否则就报错。
-- 做了非null限制的 goodstype字段，插入语句和更新语句没有 goodstype字段，无论id是否冲突 goodstype字段保存默认值0

INSERT INTO public.goods
(id, "name", "storage", count, remark, target_value, target_value_string, target_big)
VALUES(1019, '泽19', 100, NULL, '泽不存在就插入1', 100.000000, NULL, NULL)
ON CONFLICT (id) DO update SET name = EXCLUDED.name

8、
-- 没有非null限制的字段，插入语句和更新语句没有这个字段，那在数据库里就保存为null,
--  name字段没有非null限制，插入语句和更新语句没有name字段，那么表里name字段的值就保存为null
INSERT INTO public.goods
(id,  "storage", goodstype, count, remark, target_value, target_value_string, target_big, network, worker_id)
VALUES(1017400, 101, 1, NULL, '泽不存在就插入7400', 100.000000, '100.000', 10.150000, NULL, 10)
ON CONFLICT (id) DO update SET storage = EXCLUDED.storage