CDC插入postgres语句

Question

我正在尝试实现一个语句，在插入新行之前，我在其中更新一些时间戳和一个活动字段。如果信息是相同的，它不应该做任何事情。

我的声明如下：

with inputdata (id, name, inPrison)
as (
    SELECT * FROM (
        VALUES (
            1, 'egon',TRUE), (2, 'benny',FALSE), (3, 'kjeld',FALSE)) AS t (id,name,inPrison)
)
INSERT INTO public.cdc_test
           (id, NAME, inPrison,validfrom,validto,valid)
SELECT inputdata.id, inputdata.NAME, inputdata.inPrison, cdc.start, cdc.end, cdc.active
FROM inputdata 
cross JOIN (select now() as start, make_timestamptz(9999, 12, 31, 8, 00, 00) as end, TRUE as active) cdc
ON CONFLICT (id) WHERE md5(row(id,name)::text) != md5(row(public.cdc_test.id,public.cdc_test.name)::text)
DO UPDATE 
  SET valid = FALSE, 
      validto = excluded.validfrom 
      where active = TRUE

public.cdc_test表定义为：

CREATE TABLE public.cdc_test (
   id int NOT NULL,
   name char(100),
   inPrison BOOLEAN,
   validfrom DATE,
   validto DATE,
   valid BOOLEAN
);

我可以在没有开始冲突的情况下运行我的声明，但是持续的冲突不断给我带来麻烦，我觉得我已经用尽了我必须要做这件事的想法。

有人能看出我做错了什么吗？当我运行它时，我得到以下错误：invalid reference to FROM-clause entry for table "cdc_test"

Answer 1

您的ON CONFLICT应该在WHERE子句之后，而WHERE子句是SELECT语句的关联，而不是冲突处理。

在这里，您的冲突处理基于id列(可能是该表的主键)。

如果希望能够在md5(row(id,name)::text)上处理md5(row(id,name)::text)，则需要在md5(row(id,name)::text)上创建UNIQUE INDEX并在ON CONFLICT (md5(row(id,name)::text))上使用该表达式。