How to create dynamic partition table in postgres with bigint column?

Question

I have a master table such as

CREATE TABLE public.user_event_firebase
(
    user_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    event_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    "timestamp" bigint NOT NULL,
    platform character varying(255) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone DEFAULT now()
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

GOAL

I want to partition this table by year_month table with "timestamp" column such as user_event_firebase_2018_04 , user_event_firebase_2018_05, user_event_firebase_2018_06. The rows will automation redirect to insert into partition table with timestamp condition.

I created function create partition such as:

CREATE OR REPLACE FUNCTION partition_uef_table( bigint, bigint )
returns void AS $$
DECLARE
    create_query text;
    index_query text;
BEGIN
    FOR create_query, index_query IN SELECT
            'create table user_event_firebase_'
            || TO_CHAR( d, 'YYYY_MM' )
            || ' ( check( timestamp >= bigint '''
            || TO_CHAR( d, 'YYYY-MM-DD' )
            || ''' and timestamp < bigint '''
            || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
            || ''' ) ) inherits ( user_event_firebase );',
            'create index user_event_firebase_'
            || TO_CHAR( d, 'YYYY_MM' )
            || '_time on user_event_firebase_' 
            || TO_CHAR( d, 'YYYY_MM' )
            || ' ( timestamp );'
        FROM generate_series( $1, $2, '1 month' ) AS d
    LOOP
        EXECUTE create_query;
        EXECUTE index_query;
    END LOOP;
END;
$$
language plpgsql;

CREATE OR REPLACE FUNCTION test_partition_function_uef()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'insert into user_event_firebase_'
        || to_char( NEW.timestamp, 'YYYY_MM' )
        || ' values ( $1, $2, $3, $4 )' USING NEW.user_id, NEW.event_name, NEW.timestamp, NEW.platform;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

with trigger

CREATE TRIGGER test_partition_trigger_uef
    BEFORE INSERT
    ON user_event_firebase
    FOR each ROW
    EXECUTE PROCEDURE test_partition_function_uef() ;

I trying with example

SELECT partition_uef_table(1518164237,1520583437) ;

PROBLEM :

ERROR:  invalid input syntax for integer: "1 month"
LINE 14:   FROM generate_series( $1, $2, '1 month' ) AS d
                                         ^
QUERY:  SELECT
            'create table user_event_firebase_'
            || TO_CHAR( d, 'YYYY_MM' )
            || ' ( check( timestamp >= bigint '''
            || TO_CHAR( d, 'YYYY-MM-DD' )
            || ''' and timestamp < bigint '''
            || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
            || ''' ) ) inherits ( user_event_firebase );',
            'create index user_event_firebase_'

QUESTION:

How to create range for generate_series function in ' 1 month ' , set step property such int or bigint suck because of day of month is diffirence ( 2nd - 28 days, 3rd - 30 days ).

Thank you.

Possible duplicate of [Postgresql generate\_series of months](https://stackoverflow.com/questions/7450515/postgresql-generate-series-of-months) — JGH, Apr 09 '18 at 11:31

Vao Tsun · Accepted Answer · 2018-04-10T08:56:19.080

1

answer to your second question would be opinion based (so I skip it), but to the first would be such:

with args(a1,a2) as (values(1518164237,1520583437))
select d,to_char(d,'YYYY_MM') from args, generate_series(to_timestamp(a1),to_timestamp(a2),'1 month'::interval) d;

gives reult:

           d            | to_char
------------------------+---------
 2018-02-09 08:17:17+00 | 2018_02
 2018-03-09 08:17:17+00 | 2018_03
(2 rows)

Use

generate_series(start, stop, step interval) timestamp or timestamp with time zone

edited Apr 10 '18 at 08:56

answered Apr 10 '18 at 07:36

Vao Tsun

47,234
13
100
132

Excuse me, Can you write full text query? I can't use `t=# with .. ` – Loint Apr 10 '18 at 08:54
@Loint `t=#` is just psql prompt, edited to have query separate – Vao Tsun Apr 10 '18 at 08:57
1

please accept the answer and ask a new question. Don't mix several topics in one post – Vao Tsun Apr 10 '18 at 10:09
My new [question](https://stackoverflow.com/questions/49751570/whats-exactly-operator-to-compare-between-bigint-and-date-in-postgresql) – Loint Apr 10 '18 at 10:42

How to create dynamic partition table in postgres with bigint column?

1 Answers1