PostgreSQL 9.5 分区表的插入/更新

Question

我需要实现在分区表中通过ON CONFLICT()更新行的功能。
到目前为止，我的尝试如下：
表创建：

CREATE TABLE public.my_tbl
(
  goid character varying(255) NOT NULL,
  timestamps timestamp without time zone[],
  somenumber numeric[],
  CONSTRAINT my_tbl_pkey PRIMARY KEY (goid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.my_tbl
  OWNER TO postgres;

表序列：

CREATE SEQUENCE public.fixations_data_pkey_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.fixations_data_pkey_seq
  OWNER TO postgres;

表分区触发器，可以创建名称为“table_YYYY_MM_DD”的新表，其中“YYYY_MM_DD”表示当前日期（查询执行日期）：

CREATE OR REPLACE FUNCTION public.my_tbl_insert_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
    table_master    varchar(255)        := 'my_tbl';
    table_part      varchar(255)        := '';      
BEGIN
    -- Partition table name --------------------------------------------------
    table_part := table_master 
                    || '_' || DATE_PART( 'year', NOW() )::TEXT 
                    || '_' || DATE_PART( 'month', NOW() )::TEXT 
                    || '_' || DATE_PART( 'day', NOW() )::TEXT;

    -- Check if partition exists --------------------------------
    PERFORM 
        1 
    FROM 
        pg_class 
    WHERE 
        relname = table_part
    LIMIT   
        1;

    -- If not exist, create new one --------------------------------------------
    IF NOT FOUND
    THEN
        -- Create parition, which inherits master table  --------------------------
    EXECUTE '
        CREATE TABLE ' || table_part || '
        (
        goid character varying(255) NOT NULL DEFAULT nextval(''' || table_master || '_pkey_seq''::regclass),
        CONSTRAINT ' || table_part || '_pkey PRIMARY KEY (goid)
        )
        INHERITS ( ' || table_master || ' )
        WITH ( OIDS=FALSE )';

        -- Create indices for current table-------------------------------
        EXECUTE '
            CREATE INDEX ' || table_part || '_adid_date_index
            ON ' || table_part || '
            USING btree
            (goid)';
    END IF;

    -- Insert row into table (without ON CONFLICT)--------------------------------------------
    EXECUTE '
        INSERT INTO ' || table_part || ' 
        SELECT ( (' || QUOTE_LITERAL(NEW) || ')::' || TG_RELNAME || ' ).*';

    RETURN NULL;
END;
$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
ALTER FUNCTION public.my_tbl_insert_trigger()
    OWNER TO postgres;

CREATE TRIGGER my_tbl_insert_trigger
  BEFORE INSERT
  ON my_tbl
  FOR EACH ROW
  EXECUTE PROCEDURE my_tbl_insert_trigger();

我可以在此之后向表中插入新行：

INSERT INTO my_tbl (goid, timestamps, somenumber)
VALUES ('qwe123SSsssa3', '{"2016-11-16 00:00:00", "2016-11-16 01:00:00"}', '{3, 12333}')

但是当我尝试进行UPSERT时：

INSERT INTO my_tbl (goid, timestamps, somenumber)
VALUES ('qwe123SSsssa3', '{"2016-11-16 02:00:00"}', '{999}')
ON CONFLICT (goid)
DO UPDATE
SET timestamps=array_append(my_tbl.timestamps::timestamp[], '2016-11-16 02:00:00'),
somenumber=array_append(my_tbl.somenumber,'999');

我遇到了重复主键错误。
我猜想，我需要在触发器函数的第三个EXECUTE中添加ON CONFLICT。但是我应该怎么做呢？

- DocC

Answer 1

好的，我把它翻译成中文："

好的，我把我的第三个EXECUTE更改为：

"。

    -- Insert row into table (with ON CONFLICT)--------------------------------------------
EXECUTE '
INSERT INTO ' || table_part || ' 
SELECT ( (' || QUOTE_LITERAL(NEW) || ')::' || TG_RELNAME || ' ).*
ON CONFLICT (goid)
DO UPDATE
SET timestamps=' || table_part || '.timestamps::timestamp[] || ' || QUOTE_LITERAL(NEW.timestamps) || ',
somenumber=' || table_part || '.somenumber::numeric[] || ' || QUOTE_LITERAL(NEW.somenumber) || '
';

RETURN NULL;

现在，当我执行查询时：

INSERT INTO my_tbl (goid, timestamps, somenumber)
VALUES ('potato_1', ARRAY['2016-11-16 12:00:00', '2016-11-16 15:00:00']::timestamp[], ARRAY[223, 211]::numeric[]);

没有任何错误，并且它扩展了我期望的数组类型列。
我可以承认这是一个不太优雅的解决方案，但似乎它能够工作。
如果有人有更好的解决方案，我很乐意看看。

- DocC