Output of pgpartition - partition by week for 2020

pgpartition_by_week_example.sql

-- File generated at: 2020-01-19 16:34:07.677439

-- customer_transactions_y2020_w1--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w1 (
    CHECK (transasction_date >= '2019-12-30 00:00:00' AND transasction_date <= '2020-01-05 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w2--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w2 (
    CHECK (transasction_date >= '2020-01-06 00:00:00' AND transasction_date <= '2020-01-12 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w3--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w3 (
    CHECK (transasction_date >= '2020-01-13 00:00:00' AND transasction_date <= '2020-01-19 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w4--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w4 (
    CHECK (transasction_date >= '2020-01-20 00:00:00' AND transasction_date <= '2020-01-26 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w5--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w5 (
    CHECK (transasction_date >= '2020-01-27 00:00:00' AND transasction_date <= '2020-02-02 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w6--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w6 (
    CHECK (transasction_date >= '2020-02-03 00:00:00' AND transasction_date <= '2020-02-09 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w7--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w7 (
    CHECK (transasction_date >= '2020-02-10 00:00:00' AND transasction_date <= '2020-02-16 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w8--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w8 (
    CHECK (transasction_date >= '2020-02-17 00:00:00' AND transasction_date <= '2020-02-23 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w9--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w9 (
    CHECK (transasction_date >= '2020-02-24 00:00:00' AND transasction_date <= '2020-03-01 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w10--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w10 (
    CHECK (transasction_date >= '2020-03-02 00:00:00' AND transasction_date <= '2020-03-08 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w11--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w11 (
    CHECK (transasction_date >= '2020-03-09 00:00:00' AND transasction_date <= '2020-03-15 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w12--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w12 (
    CHECK (transasction_date >= '2020-03-16 00:00:00' AND transasction_date <= '2020-03-22 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w13--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w13 (
    CHECK (transasction_date >= '2020-03-23 00:00:00' AND transasction_date <= '2020-03-29 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w14--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w14 (
    CHECK (transasction_date >= '2020-03-30 00:00:00' AND transasction_date <= '2020-04-05 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w15--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w15 (
    CHECK (transasction_date >= '2020-04-06 00:00:00' AND transasction_date <= '2020-04-12 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w16--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w16 (
    CHECK (transasction_date >= '2020-04-13 00:00:00' AND transasction_date <= '2020-04-19 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w17--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w17 (
    CHECK (transasction_date >= '2020-04-20 00:00:00' AND transasction_date <= '2020-04-26 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w18--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w18 (
    CHECK (transasction_date >= '2020-04-27 00:00:00' AND transasction_date <= '2020-05-03 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w19--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w19 (
    CHECK (transasction_date >= '2020-05-04 00:00:00' AND transasction_date <= '2020-05-10 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w20--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w20 (
    CHECK (transasction_date >= '2020-05-11 00:00:00' AND transasction_date <= '2020-05-17 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w21--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w21 (
    CHECK (transasction_date >= '2020-05-18 00:00:00' AND transasction_date <= '2020-05-24 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w22--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w22 (
    CHECK (transasction_date >= '2020-05-25 00:00:00' AND transasction_date <= '2020-05-31 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w23--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w23 (
    CHECK (transasction_date >= '2020-06-01 00:00:00' AND transasction_date <= '2020-06-07 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w24--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w24 (
    CHECK (transasction_date >= '2020-06-08 00:00:00' AND transasction_date <= '2020-06-14 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w25--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w25 (
    CHECK (transasction_date >= '2020-06-15 00:00:00' AND transasction_date <= '2020-06-21 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w26--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w26 (
    CHECK (transasction_date >= '2020-06-22 00:00:00' AND transasction_date <= '2020-06-28 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w27--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w27 (
    CHECK (transasction_date >= '2020-06-29 00:00:00' AND transasction_date <= '2020-07-05 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w28--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w28 (
    CHECK (transasction_date >= '2020-07-06 00:00:00' AND transasction_date <= '2020-07-12 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w29--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w29 (
    CHECK (transasction_date >= '2020-07-13 00:00:00' AND transasction_date <= '2020-07-19 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w30--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w30 (
    CHECK (transasction_date >= '2020-07-20 00:00:00' AND transasction_date <= '2020-07-26 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w31--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w31 (
    CHECK (transasction_date >= '2020-07-27 00:00:00' AND transasction_date <= '2020-08-02 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w32--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w32 (
    CHECK (transasction_date >= '2020-08-03 00:00:00' AND transasction_date <= '2020-08-09 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w33--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w33 (
    CHECK (transasction_date >= '2020-08-10 00:00:00' AND transasction_date <= '2020-08-16 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w34--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w34 (
    CHECK (transasction_date >= '2020-08-17 00:00:00' AND transasction_date <= '2020-08-23 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w35--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w35 (
    CHECK (transasction_date >= '2020-08-24 00:00:00' AND transasction_date <= '2020-08-30 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w36--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w36 (
    CHECK (transasction_date >= '2020-08-31 00:00:00' AND transasction_date <= '2020-09-06 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w37--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w37 (
    CHECK (transasction_date >= '2020-09-07 00:00:00' AND transasction_date <= '2020-09-13 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w38--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w38 (
    CHECK (transasction_date >= '2020-09-14 00:00:00' AND transasction_date <= '2020-09-20 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w39--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w39 (
    CHECK (transasction_date >= '2020-09-21 00:00:00' AND transasction_date <= '2020-09-27 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w40--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w40 (
    CHECK (transasction_date >= '2020-09-28 00:00:00' AND transasction_date <= '2020-10-04 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w41--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w41 (
    CHECK (transasction_date >= '2020-10-05 00:00:00' AND transasction_date <= '2020-10-11 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w42--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w42 (
    CHECK (transasction_date >= '2020-10-12 00:00:00' AND transasction_date <= '2020-10-18 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w43--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w43 (
    CHECK (transasction_date >= '2020-10-19 00:00:00' AND transasction_date <= '2020-10-25 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w44--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w44 (
    CHECK (transasction_date >= '2020-10-26 00:00:00' AND transasction_date <= '2020-11-01 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w45--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w45 (
    CHECK (transasction_date >= '2020-11-02 00:00:00' AND transasction_date <= '2020-11-08 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w46--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w46 (
    CHECK (transasction_date >= '2020-11-09 00:00:00' AND transasction_date <= '2020-11-15 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w47--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w47 (
    CHECK (transasction_date >= '2020-11-16 00:00:00' AND transasction_date <= '2020-11-22 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w48--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w48 (
    CHECK (transasction_date >= '2020-11-23 00:00:00' AND transasction_date <= '2020-11-29 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w49--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w49 (
    CHECK (transasction_date >= '2020-11-30 00:00:00' AND transasction_date <= '2020-12-06 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w50--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w50 (
    CHECK (transasction_date >= '2020-12-07 00:00:00' AND transasction_date <= '2020-12-13 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w51--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w51 (
    CHECK (transasction_date >= '2020-12-14 00:00:00' AND transasction_date <= '2020-12-20 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w52--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w52 (
    CHECK (transasction_date >= '2020-12-21 00:00:00' AND transasction_date <= '2020-12-27 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_y2020_w53--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_y2020_w53 (
    CHECK (transasction_date >= '2020-12-28 00:00:00' AND transasction_date <= '2021-01-03 23:59:59' )
) INHERITS (customer_transactions);
-- customer_transactions_catch_all--------------------------------------------------------

CREATE TABLE IF NOT EXISTS customer_transactions_catch_all (
    CHECK (transasction_date >= '2021-01-04 00:00:00' OR transasction_date <= '2019-12-29 23:59:59' )
) INHERITS (customer_transactions);


----- Create the insert trigger function ---------------------------------------
CREATE OR REPLACE FUNCTION customer_transactions_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
  target_partitioned_by text;
  table_name text;
BEGIN
    SELECT cast(extract(week from NEW.transasction_date) AS TEXT) INTO target_partitioned_by;
    table_name = 'customer_transactions_y2020_w' || target_partitioned_by;

    -- RAISE NOTICE 'Table name %', table_name;

    IF (NEW.transasction_date >= '2019-12-30 00:00:00' AND NEW.transasction_date <= '2021-01-03 23:59:59' ) THEN EXECUTE 'INSERT INTO ' || table_name || ' SELECT * FROM (SELECT $1.*) AS t' USING NEW;
    ELSE
        INSERT INTO customer_transactions_catch_all VALUES (NEW.*);
        RAISE NOTICE 'Date out of range. Inserting into the catch all partition...';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

----- Attach before insert trigger to table --------
DROP TRIGGER IF EXISTS before_insert_customer_transactions_trigger on customer_transactions;
CREATE TRIGGER before_insert_customer_transactions_trigger
BEFORE INSERT ON customer_transactions
FOR EACH ROW EXECUTE PROCEDURE customer_transactions_insert_trigger();