在 PostgreSQL 中，ON CONFLICT 子句是用在 INSERT 語句中的一種機制，它可以幫助你處理當插入操作遇到違反唯一性約束（比如唯一索引或主鍵約束）時的情況。使用 ON CONFLICT 子句，你可以指定當違反唯一性約束時應(yīng)該采取的操作，比如忽略這個插入，或者更新已經(jīng)存在的行。

ON CONFLICT (sample_id_lims) DO UPDATE 是指當你在插入數(shù)據(jù)時，如果 sample_id_lims 字段的值導(dǎo)致了唯一性約束的沖突，那么不是放棄這次插入，而是更新已經(jīng)存在的那一行數(shù)據(jù)。

這里有一個具體的例子：

假設(shè)你有一個名為 samples 的表，它有一個名為 sample_id_lims 的字段，該字段上有一個唯一索引。現(xiàn)在，你想插入一個新的樣本數(shù)據(jù)，但如果 sample_id_lims 的值已經(jīng)存在，你希望更新這條記錄的其他字段而不是放棄插入。

INSERT INTO samples (sample_id_lims, data_field1, data_field2)
VALUES ('123', 'New Data 1', 'New Data 2')
ON CONFLICT (sample_id_lims) DO UPDATE
SET data_field1 = EXCLUDED.data_field1,
    data_field2 = EXCLUDED.data_field2;

在這個例子中，如果 sample_id_lims 為 '123' 的記錄已經(jīng)存在，那么 ON CONFLICT 子句會觸發(fā)，并執(zhí)行 DO UPDATE 操作。SET 子句用于更新沖突行的 data_field1 和 data_field2 字段。關(guān)鍵字 EXCLUDED 用來引用那些原本嘗試插入但發(fā)生沖突的值。
通過使用 ON CONFLICT 子句，你可以確保表中的數(shù)據(jù)保持唯一性，同時仍然可以通過更新操作來應(yīng)對重復(fù)的插入嘗試。

ON CONFLICT 子句在 PostgreSQL 中的使用不僅限于簡單的 UPDATE 操作。這里有一些擴展用法：

1、指定唯一約束名稱:如果表中有多個唯一約束，你可以通過唯一約束的名稱指定應(yīng)對哪個約束的沖突。

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON CONFLICT ON CONSTRAINT constraint_name
DO NOTHING; -- 或者 DO UPDATE ...

2、條件更新:在執(zhí)行 UPDATE 操作時，可以加入 WHERE 子句來設(shè)置條件，僅在滿足某些條件時才更新。

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE
SET column2 = EXCLUDED.column2
WHERE table_name.column3 > 10;

3、使用 DO NOTHING:如果你不想在發(fā)生沖突時執(zhí)行任何操作，可以使用 DO NOTHING。這樣，如果插入的數(shù)據(jù)違反了唯一性約束，PostgreSQL 會忽略這個插入，并且不會報錯。

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON CONFLICT (column1) DO NOTHING;

4、返回插入或更新后的行:通過 RETURNING 子句，你可以在 INSERT 操作完成后返回插入或更新的行的信息。

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE
SET column2 = EXCLUDED.column2
RETURNING *;

5、使用 WHERE 子句過濾沖突的行:你可以在 ON CONFLICT 子句中使用 WHERE 來過濾哪些沖突行應(yīng)該被更新。

INSERT INTO table_name (column1, column2, status) VALUES (value1, value2, 'pending')
ON CONFLICT (column1) WHERE (table_name.status = 'active') DO UPDATE
SET column2 = EXCLUDED.column2;

上面的例子中，只有當沖突行的 status 字段為 'active' 時，才會執(zhí)行 UPDATE 操作。

使用 ON CONFLICT 子句可以幫助你以一種優(yōu)雅的方式處理可能的數(shù)據(jù)插入沖突，確保數(shù)據(jù)的完整性，同時還能靈活地進行各種條件處理。

on conflict 用法真的很方便，不存在就插入，存在可以更新 可以do nothing，就是用起來要注意幾點：

• 字段必須完全同名。試了半天不行，改了同名就行。
• 拿不到原表的內(nèi)容。退而求其次用原表做一個內(nèi)連接就行，取原值然后想怎么玩都行。下面這個例子就是取原值并加上新統(tǒng)計值更新回原表。
• 建臨時表可以加入很多自己需要的內(nèi)容，操作空間更大
• excluded代表新選擇出來的內(nèi)容。

	insert into plate_no_info  
	select plate_no,total_orders ,first_parking_time,last_parking_time
	from (
	select t1.plate_no,(plate_no_info.total_orders + cnt ) as total_orders,plate_no_info.first_parking_time,t1.last_parking_time
	from (
	select plate_no,count(id) cnt,min(parking_time) first_parking_time,max(parking_time) last_parking_time
	from order_list 
	WHERE created_at > CURRENT_DATE - 1
	and plate_no ~'^[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'
	GROUP BY plate_no
	) t1,
	plate_no_info
	WHERE 1=1
	AND t1.plate_no = plate_no_info.plate_no
	)
	as t_temp
	on conflict(plate_no) 
-- 	do NOTHING
	do update 
	set 
	total_orders = excluded.total_orders,
	first_parking_time = excluded.first_parking_time,
	last_parking_time = excluded.last_parking_time;

總結(jié)

到此這篇關(guān)于PostgreSQL中ON CONFLICT的使用及一些擴展用法的文章就介紹到這了,更多相關(guān)PGSQL中ON CONFLICT使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論