PostgreSQL 9.5 Insert/Update while partitioning table
<p>I need to achieve updating (via ON CONFLICT()) row in a partitioned tables. <BR/>
So far, my tries: <BR/>
Table creation:</p>

<pre><code>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;
</code></pre>

<p>Table Sequence:</p>

<pre><code>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;
</code></pre>

<p>Table partition trigger, which creates new table with name "table_YYYY_MM_DD", where "YYYY_MM_DD" - current date (query execution date):</p>

<pre><code>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();
</code></pre>

<p>After this I can insert new rows into table:</p>

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

<p>But when I'm trying to do UPSERT:</p>

<pre><code>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');
</code></pre>

<p>I'm geting DUPLICATE PKEY error. <BR/>
I guess, that I have to add ON CONFLICT to third EXECUTE in trigger function. But how should I do this?</p>