title: PostgreSQL 9.5 Insert/Update while partitioning table
body: <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>