Records not getting inserted in specified POSTGRES DATABASE PARTITION

Question

I created a MASTER PARTITION TABLE with RANGE Partition with the command:

CREATE TABLE users_range_part (
    user_id SERIAL,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (created_dt, user_id)
) PARTITION BY RANGE(created_dt)

Afterwhich I created a DEFAULT CHILD PARTITON for the MASTER PARTITION, this table is meant to hold records that dont fall within the range of future CHILD PARTITION TABLE I will create... SO I created the DEFAULT CHILD PARTITON with the command:

CREATE TABLE users_range_part_default
PARTITION OF users_range_part DEFAULT

NEXT: Now my goal is to partition every record by their month according to the order_date which I specified at the beginning

SO I created Partition for every month starting from 2016 to 2020

I did this with the command:

import psycopg2

import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd

months = pd.date_range(start='1/1/2016', end='12/31/2020', freq='1M')

connection = psycopg2.connect(
    host='localhost',
    port='5433',
    database='pat_db',
    user='postgres',
    password='pass'
)
cursor = connection.cursor()
table_name = 'users_range_part'
query = '''
CREATE TABLE {table_name}_{yyyymm}
PARTITION OF {table_name}
FOR VALUES FROM ('{begin_date}') TO ('{end_date}')
'''
for month in months:
    begin_date = month - MonthBegin(1)
    end_date = month + MonthEnd(0)
    print(f'Adding partition for {begin_date} and {end_date}')
    cursor.execute(
        query.format(
            table_name=table_name,
            yyyymm=str(month)[:7].replace('-', ''),
            begin_date=str(begin_date).split(' ')[0],
            end_date=str(end_date).split(' ')[0]
        ), ()
    )
connection.commit()
cursor.close()
connection.close()

I confirmed the creation of all this CHILD PARTITIONS:

tablename                 tableowner
users                     postgres
users_part                postgres
users_part_a              postgres
users_part_default        postgres
users_part_u              postgres
users_range_part          postgres
users_range_part_2017     postgres
users_range_part_2018     postgres
users_range_part_default    postgres
users_range_part_2016     postgres
users_range_part_2019     postgres
users_range_part_2020     postgres
users_range_part_201612     postgres
users_range_part_201701     postgres
users_range_part_201601     postgres
users_range_part_201602     postgres
users_range_part_201603     postgres
users_range_part_201604     postgres
users_range_part_201605     postgres
users_range_part_201606     postgres
users_range_part_201607     postgres
users_range_part_201608     postgres
users_range_part_201609     postgres
users_range_part_201610     postgres
users_range_part_201611     postgres
users_range_part_201702     postgres
users_range_part_201703     postgres
users_range_part_201704     postgres
users_range_part_201705     postgres
users_range_part_201706     postgres
users_range_part_201707     postgres
users_range_part_201708     postgres
users_range_part_201709     postgres
users_range_part_201710     postgres
users_range_part_201711     postgres
users_range_part_201712     postgres
users_range_part_201801     postgres
users_range_part_201802     postgres
users_range_part_201803     postgres
users_range_part_201804     postgres
users_range_part_201805     postgres
users_range_part_201806     postgres
users_range_part_201807     postgres
users_range_part_201808     postgres
users_range_part_201809     postgres
users_range_part_201810     postgres
users_range_part_201811     postgres
users_range_part_201812     postgres
users_range_part_201901     postgres
users_range_part_201902     postgres
users_range_part_201903     postgres
users_range_part_201904     postgres
users_range_part_201905     postgres
users_range_part_201906     postgres
users_range_part_201907     postgres
users_range_part_201908     postgres
users_range_part_201909     postgres
users_range_part_201910     postgres
users_range_part_201911     postgres
users_range_part_201912     postgres
users_range_part_202001     postgres
users_range_part_202002     postgres
users_range_part_202003     postgres
users_range_part_202004     postgres
users_range_part_202005     postgres
users_range_part_202006     postgres
users_range_part_202007     postgres
users_range_part_202008     postgres
users_range_part_202009     postgres
users_range_part_202010     postgres
users_range_part_202011     postgres
users_range_part_202012     postgres

Now: It is time to test my PARTITONS

SO I inserted some records

INSERT INTO users_range_part 
    (user_first_name, user_last_name, user_email_id, created_dt)
VALUES 
    ('Scott', 'Tiger', 'scott@tiger.com', '2018-10-01'),
    ('Donald', 'Duck', 'donald@duck.com', '2019-02-10'),
    ('Mickey', 'Mouse', 'mickey@mouse.com', '2017-06-22')

This worked perfectly as they all got partitioned to their respective CHILD PARTITONED TABLE

So obviously my DEFAULT CHILD PARTITION TABLE should have no values

NOW the issue comes with inserting new records:

INSERT INTO users_range_part 
    (user_first_name, user_last_name, user_email_id, created_dt)
VALUES 
    ('Dee', 'Vic', 'dott@tiger.com', '2018-10-31'),
    ('Don', 'Jay', 'don@duck.com', '2019-12-31'),
    ('Mik', 'Mousse', 'mick@mousse.com', '2017-09-30')

When I run this, the expected output is to still partition the records according to their months but the records dont get moved but are still kept in the DEFAULT CHILD PARTITION... Please help

Records not getting inserted in specified POSTGRES DATABASE PARTITION

0 Answers