0

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.

2
  • Possible duplicate of Postgresql generate_series of months Commented Apr 9, 2018 at 11:31
  • @JGH The answer in this question too complicated with me Commented Apr 10, 2018 at 10:02

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

4 Comments

Excuse me, Can you write full text query? I can't use t=# with ..
@Loint t=# is just psql prompt, edited to have query separate
please accept the answer and ask a new question. Don't mix several topics in one post
My new question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.