时间戳

首页 > 解决方案 > 使用具有模式匹配的唯一约束

postgresql - 使用具有模式匹配的唯一约束

问题描述

我的表有三个字段，当将每一行组合在一起时，它们必须是唯一的。我的数据更改 cte 不会根据所述条件在该表中输入新行，但会继续在其相关表中输入行。

请参阅以下内容：

CREATE TABLE IF NOT EXISTS e_tests(
  e_id BIGSERIAL,
  e_name VARCHAR(50),
  con VARCHAR(30),
  start_time TIMESTAMPTZ,
  PRIMARY KEY (e_id),
  UNIQUE (e_name, con, start_time)
);

CREATE TABLE IF NOT EXISTS fdf_tests(
  b_id BIGSERIAL,
  e_ref BIGSERIAL,
  v VARCHAR(20),
  type VARCHAR(92),
  FOREIGN KEY (e_ref) REFERENCES e_tests(event_id),
  PRIMARY KEY (b_id)
);


CREATE OR REPLACE PROCEDURE insert_on_conflict(input jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  WITH ins_e AS (
    INSERT INTO e_tests (e_name, start_time, con)
    SELECT inp->>'e_name' e_name, (inp->>'start_time')::timestamp start_time, inp->>'con' con
    FROM jsonb_array_elements(input) inp
    ON CONFLICT (e_name, start_time, con) DO UPDATE SET e_name=EXCLUDED.e_name
    RETURNING event_id, e_name
  ),
...Rest of procedure

这一切都很好，但我需要对 e_name 使用模式匹配。因此，如果我们有一行其中 e_name 是“某个字符串”，并且我们要插入的新行之一具有相同的 start_time 和 con，我们将不会插入新行。

例如，如果 e_tests 表是这样的：

e_id     e_name         con      start_time
--------------------------------------------------
1     'some string'     'ab'      some date

以下插入不起作用：

INSERT INTO e_tests(e_name, con, start_time)
VALUES('some stri', 'ab', 'some date')

但是这个插入将起作用：

INSERT INTO e_tests(e_name, con, start_time)
VALUES('some stri', 'ab', 'some other date')

我如何实现这一目标？

** 更新 **

我能想到的不使用循环或通过中间表插入数据的唯一方法可能是

CREATE TABLE IF NOT EXISTS e_tests(
  e_id BIGSERIAL,
  e_name VARCHAR(50),
  con VARCHAR(30),
  start_time TIMESTAMPTZ,
  PRIMARY KEY (e_id),
  --UNIQUE (e_name, con, start_time)
  EXCLUDE USING gist (e_name WITH =, start_time WITH =)
);

但我无法引用排除或检查约束中的现有行，所以......

是否必须用for循环一一插入？（太令人失望了，我认为 postgres 很神奇）

标签： postgresqlplpgsql

解决方案

这不能在冲突条款中完成。它只接受索引名称或索引列，如下所述：https ://www.postgresql.org/docs/11/sql-insert.html

您应该考虑在代码中编写“手动”检查。

最好的问候，
Bjarni

推荐阅读