我有一張桌子：
我想要插入或更新從另一個表中獲取的資料。我的約束是三個欄位：id_1、id_2 和日期。它的欄位必須是唯一的。如果我做：
ALTER TABLE my_table
ADD CONSTRAINT constr_1 UNIQUE (id_1, id_2, date);
進而：
insert into my_table
(id_1, id_2, quantity, date)
values (1, null, 5, '2022-04-27'), -- values I get another select request
(null, 5, 5, '2022-04-27'), -- this means that the values can be different
(99, 85, 100, '2022-04-29')
ON CONFLICT (id_1, id_2, date)
DO Update
SET quantity = excluded.quantity
約束不起作用，我只是插入具有相同值的新行。如何在沒有記錄的情況下插入并在其存在時更新？
uj5u.com熱心網友回復：
PostgreSQL 將 NULL 視為不同的值，因此，在具有 UNIQUE 索引的列中可以有多個 NULL 值。當您為表定義主鍵或唯一約束時，PostgreSQL 會自動創建相應的 UNIQUE 索引。
解決方案是使用合并創建一個唯一索引。在此示例中，我使用coalesce(~, 0)了這意味著 null 和 0 被視為同一事物。您可能更喜歡使用另一個值，例如 int 的最大可能值為 2147483648。請注意，我們必須修改 ON CONFLICT 串列以匹配索引。
CREATE temp TABLE my_table ( id_1 int, id_2 int, quantity numeric, mytable_date date );?
CREATE UNIQUE INDEX my_table_unique ON my_table (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'));?
INSERT INTO my_table (id_1, id_2, quantity, mytable_date) VALUES (1, NULL, 5, '2022-04-27'), (NULL, 5, 5, '2022-04-27'), (99, 85, 100, '2022-04-29') ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01')) DO UPDATE SET quantity = excluded.quantity; INSERT INTO my_table (id_1, id_2, quantity, mytable_date) VALUES (99, 85, 101, '2022-04-29') ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01')) DO UPDATE SET quantity = excluded.quantity;
3 行受影響
1 行受影響
select * from my_table;id_1 | id_2 | 數量 | mytable_date ---: | ---: | --------: | :----------- 1 | 空| 5 | 2022-04-27 空| 5 | 5 | 2022-04-27 99 | 85 | 101 | 2022-04-29
*db<>在這里小提琴74bf159a4d041c31fec5f)
uj5u.com熱心網友回復：
CREATE temp TABLE my_table (
id_1 int,
id_2 int,
quantity numeric,
mytable_date date
);
ALTER TABLE my_table
ADD CONSTRAINT constr_1 UNIQUE (id_1, id_2, mytable_date);
然后
INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
VALUES (1, NULL, 5, '2022-04-27'), (NULL, 5, 5, '2022-04-27'), (99, 85, 100, '2022-04-29')
ON CONFLICT (id_1, id_2, mytable_date)
DO UPDATE SET
quantity = excluded.quantity;
INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
VALUES (99, 85, 101, '2022-04-29')
ON CONFLICT (id_1, id_2, mytable_date)
DO UPDATE SET
quantity = excluded.quantity;
請查看手冊部分：5.4.3。關于特殊情況的唯一約束： null。
通常，如果表中有不止一行，其中包含在約束中的所有列的值都相等，則違反了唯一約束。但是，在此比較中，兩個空值永遠不會被視為相等。這意味著即使存在唯一約束，也可以在至少一個受約束的列中存盤包含空值的重復行。此行為符合 SQL 標準，但我們聽說其他 SQL 資料庫可能不遵循此規則。因此，在開發旨在可移植的應用程式時要小心。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/467478.html
標籤：PostgreSQL 上插
上一篇：在Django模型JSONField中的字典串列中進行查詢
下一篇：創建新的sql列以獲取新值