PostgreSQL 9.5在分区表时插入/更新

问题描述：

我需要在分区表中实现更新（通过ON CONFLICT（））行。
到目前为止，我的尝试：
创建表：PostgreSQL 9.5在分区表时插入/更新

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');

我歌厅DUPLICATE PKEY错误。
我想，我必须在触发功能中将ON CONFLICT添加到第三个EXECUTE。但我应该怎么做？

答

嗯，我改变了我的第三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[]);

没有任何错误，它扩展了数组类型的列我预计
我可以承认，这是一个肮脏的解决方案，但它似乎工作。
如果有人有更好的解决方案，我很乐意看看它。

PostgreSQL 9.5在分区表时插入/更新

相关推荐