CODEWITHSUNDEEP
sqlpostgresqlpartitioningupsert
DocC
DocC

Reputation: 385

PostgreSQL 9.5 Insert/Update while partitioning table

I need to achieve updating (via ON CONFLICT()) row in a partitioned tables.
So far, my tries:
Table creation:

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;

Table Sequence:

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 partition trigger, which creates new table with name "table_YYYY_MM_DD", where "YYYY_MM_DD" - current date (query execution date):

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

After this I can insert new rows into table:

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

But when I'm trying to do 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');

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

Upvotes: 3

Views: 1591

Answers (1)

DocC
DocC

Reputation: 385

Well , I've changed my third EXECUTE to : 

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

Now, when I execute query:

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[]);

there are no any errors, and it extends array-type columns as I expected
I can admit that this is a dirty solution, but it seems that it works.
If someone has a better solution, I'll glad to look at it.

Upvotes: 1

Related Questions