0Aw3NJc1

1CREATE TABLE public.my_tbl
2(
3  goid character varying(255) NOT NULL,
4  timestamps timestamp without time zone[],
5  somenumber numeric[],
6  CONSTRAINT my_tbl_pkey PRIMARY KEY (goid)
7)
8WITH (
9  OIDS=FALSE
10);
11ALTER TABLE public.my_tbl
12  OWNER TO postgres;
13	
14CREATE SEQUENCE public.fixations_data_pkey_seq
15  INCREMENT 1
16  MINVALUE 1
17  MAXVALUE 9223372036854775807
18  START 1
19  CACHE 1;
20ALTER TABLE public.fixations_data_pkey_seq
21  OWNER TO postgres;
22	
23CREATE OR REPLACE FUNCTION public.my_tbl_insert_trigger()
24  RETURNS trigger AS
25$BODY$
26DECLARE
27    table_master    varchar(255)        := 'my_tbl';
28    table_part      varchar(255)        := '';      
29BEGIN
30    -- Partition table name --------------------------------------------------
31    table_part := table_master 
32                    || '_' || DATE_PART( 'year', NOW() )::TEXT 
33                    || '_' || DATE_PART( 'month', NOW() )::TEXT 
34                    || '_' || DATE_PART( 'day', NOW() )::TEXT;
35
36    -- Check if partition exists --------------------------------
37    PERFORM 
38        1 
39    FROM 
40        pg_class 
41    WHERE 
42        relname = table_part
43    LIMIT   
44        1;
45
46    -- If not exist, create new one --------------------------------------------
47    IF NOT FOUND
48    THEN
49        -- Create parition, which inherits master table  --------------------------
50    EXECUTE '
51        CREATE TABLE ' || table_part || '
52        (
53        goid character varying(255) NOT NULL DEFAULT nextval(''' || table_master || '_pkey_seq''::regclass),
54        CONSTRAINT ' || table_part || '_pkey PRIMARY KEY (goid)
55        )
56        INHERITS ( ' || table_master || ' )
57        WITH ( OIDS=FALSE )';
58
59        -- Create indices for current table-------------------------------
60        EXECUTE '
61            CREATE INDEX ' || table_part || '_adid_date_index
62            ON ' || table_part || '
63            USING btree
64            (goid)';
65    END IF;
66
67    -- Insert row into table (without ON CONFLICT)--------------------------------------------
68    EXECUTE '
69        INSERT INTO ' || table_part || ' 
70        SELECT ( (' || QUOTE_LITERAL(NEW) || ')::' || TG_RELNAME || ' ).*';
71
72    RETURN NULL;
73END;
74$BODY$
75    LANGUAGE plpgsql VOLATILE
76    COST 100;
77ALTER FUNCTION public.my_tbl_insert_trigger()
78    OWNER TO postgres;
79	
80INSERT INTO my_tbl (goid, timestamps, somenumber)
81VALUES ('qwe123SSsssa3', '{"2016-11-16 00:00:00", "2016-11-16 01:00:00"}', '{3, 12333}')
82	
83INSERT INTO my_tbl (goid, timestamps, somenumber)
84VALUES ('qwe123SSsssa3', '{"2016-11-16 02:00:00"}', '{999}')
85ON CONFLICT (goid)
86DO UPDATE
87SET timestamps=array_append(my_tbl.timestamps::timestamp[], '2016-11-16 02:00:00'),
88somenumber=array_append(my_tbl.somenumber,'999');