Partition Expression
Hi Team,
i need to write a partition expression based on dates and hour .use case is like this.
Dates
2019-01-01 00:02:22
2019-01-01 03:02:22
2019-01-01 10:02:22
2019-01-01 23:02:22
2019-01-01 15:02:22
2019-02-01 09:02:22
2019-02-01 04:02:22
2019-02-01 23:02:22
Here i have data and hours.My requirement is i need to have 2 partitions for each day where 1st one will hold hours from 00-11 and the other partition for the same day will have 12-23. Similar for the 2nd day as well.
o/p
2019-01-01 (00-11)
2019-01-01 (12-23)
2019-02-01 (00-11)
2019-02-01 (12-23)
We can add suffix also to add at the end of partition so as to identify which partition belong to which hour.
i am using the below syntax but where to add the hour part ,
PARTITION BY ((((date_part('year', time) * 10000) + (date_part('month', time) * 100)) + date_part('day', time)))
Answers
Here's one way...
dbadmin=> CREATE TABLE part_test (c TIMESTAMP NOT NULL) PARTITION BY EXTRACT(YEAR FROM c) || EXTRACT(DAY FROM c) || EXTRACT(MONTH FROM c) || CASE WHEN EXTRACT(HOUR FROM c) < 12 THEN 0 ELSE 1 END;; CREATE TABLE dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 00:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 03:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 10:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 23:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 15:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 09:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 04:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 23:02:22'; OUTPUT -------- 1 (1 row) dbadmin=> COMMIT; dbadmin=> SELECT do_tm_task('mergeout', 'part_test'); do_tm_task ----------------------------------------------------------------------------------------------------------------------------------------- Task: mergeout (Table: public.part_test) (Projection: public.part_test_b0) (Table: public.part_test) (Projection: public.part_test_b1) (1 row) dbadmin=> SELECT partition_key, SUM(ros_row_count) ros_row_count FROM partitions WHERE projection_name = 'part_test_b0' GROUP BY 1 ORDER BY 1; partition_key | ros_row_count ---------------+--------------- 2019110 | 3 2019111 | 2 2019120 | 2 2019121 | 1 (4 rows)