sql - 避免在 postgreSQL 中重复

Question

我使用的是 postgreSQL 10.3 版，我有一个这样的表:

CREATE TABLE IF NOT EXISTS example (
  id SERIAL PRIMARY KEY, 
  first VARCHAR(64) NOT NULL, 
  second VARCHAR(255) NOT NULL, 
  third VARCHAR (255), 
  fourth VARCHAR (4) NOT NULL, 
  fifth VARCHAR(12) NOT NULL);

我想制作多个 INSERT 并确保没有重复项。所以我考虑使用 INSERT INTO ... ON CONFLICT DO NOTHING;。

想法是仅当列上的值 first | 时才插入记录第二 |第三 |第四| fifth 尚未出现在表中。问题是 id 是自动递增的，所以每条记录都是不同的，因此 INSERT 总是发生。

这里我们明白我的意思了:

db=# CREATE TABLE IF NOT EXISTS example (id SERIAL PRIMARY KEY, first VARCHAR(64) NOT NULL, second VARCHAR(255) NOT NULL, third VARCHAR (255), fourth VARCHAR (4) NOT NULL, fifth VARCHAR(12) NOT NULL);
CREATE TABLE
db=# \dt
           Lista delle relazioni
 Schema |  Nome   |  Tipo   | Proprietario
--------+---------+---------+--------------
 public | example | tabella | admin
(1 riga)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('1', '2', '3', '4', '5') ON CONFLICT DO NOTHING;
INSERT 0 1

db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
(1 riga)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('11', '22', '33', '44', '55') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
(2 righe)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('111', '222', '333', '444', '555') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
  3 | 111   | 222    | 333   | 444    | 555
(3 righe)


db=# INSERT INTO example (first, second, third, fourth, fifth) VALUES ('1', '2', '3', '4', '5') ON CONFLICT DO NOTHING;
INSERT 0 1
db=# SELECT * FROM example;
 id | first | second | third | fourth | fifth
----+-------+--------+-------+--------+-------
  1 | 1     | 2      | 3     | 4      | 5
  2 | 11    | 22     | 33    | 44     | 55
  3 | 111   | 222    | 333   | 444    | 555
  4 | 1     | 2      | 3     | 4      | 5
(4 righe)

记录 (2 | 11 | 22 | 33 | 44 | 55) 等于记录 (4 | 1 | 2 | 3 | 4 | 5) .它们仅在 id 上有所不同。我不希望插入记录 (4 | 1 | 2 | 3 | 4 | 5)。我该怎么办？

谢谢

Answer 1

这个

ALTER TABLE example ADD CONSTRAINT constraintname UNIQUE (first, 
second, third, fourth, fifth);

将不起作用，因为列可以为空:

third VARCHAR (255)

你可以这样检查:

INSERT INTO example(first, second, third, fourth, fifth) values('1', '2', NULL, '4', '5');
INSERT INTO example(first, second, third, fourth, fifth) values('1', '2', NULL, '4', '5)';

SELECT * FROM example;

输出:

|id|first|second|third|fourth|fifth|
|1|1|2|NULL|4|5|
|2|1|2|NULL|4|5|

你应该:

1. 设置非空:ALTER TABLE 示例 ALTER COLUMN 第三个 SET NOT NULL;

2. 具有 NULL 和 NOT NULL 条件的两个索引:

   CREATE UNIQUE INDEX ON example(first, second, fourth, fifth) WHERE third IS NULL;
   CREATE UNIQUE INDEX ON example(first, second, third, fourth, fifth) WHERE third IS NOT NULL;
   

3. 如果 COALSCE(third,'') 适合您的情况，则:

   CREATE UNIQUE INDEX ON example (first, second, COALESCE(third, ''), fourth, fifth);