Table Inheritance , Partitioning in PostgreSQL

Table Inheritance is a concept from object-oriented PostgreSQL databases. We can apply parents and child relationship between tables.

Example : We will create Order and Online booking or Agent tables.

#create table order(order_id serial,flight_name varchar , status varchar );

#create table online_booking(price numeric) inherits (order);

#create table agent(commission int) inherits (order);

#insert into online_booking(light_name , status , price) values('Air India','Online',1000);

#insert into agent(light_name , status , commission) values('Air India','Online',300);

#select * from online_booking ; It will show all data base on online_booking 

#select * from agent; It will show all data base on agent;

#select * from only order ; It will show empty table due to base table not have any data.

Other example:  for Advisory

Table Partitioning : 

• Table Partitioning means splitting a table into smaller pieces.
• Table Partitioning holds many performance benefits for tables that hold large amount of data.
• PostgreSQL allows table partitioning via table inheritance .
• Each partitioning is created as child table of a single parent table.
• PostgreSQL implements Range , Hash and List partitioning methods.

LIST PARTITION :

TestDB# CREATE TABLE emp ( empid integer , empstatus varchar (20)) partition by list (employees);

TestDB# CREATE TABLE emp1 partition of emp for values in ('PERMANENT);

TestDB# CREATE TABLE emp2 partition of emp for values in ('CONTRACT);

TestDB# CREATE TABLE emp3 partition of emp default;

INSERT INTO emp values (1,'PERMANENT'),(2,'CONTRACT'),(3,'CO-OP STUDENT'),(4,'TRAINEES')

RETURNING *;

Note : Let me see which partition each employee has gone to .

#SELECT tableoid::regclass, * from emp;

Range Partition :

TestDB #create table emp (empid int,empstatus varchar ,salary int) partition by range (salary);

TestDB #create table emp1 partition of emp1 for values from (minvalue) to (1000);

TestDB #create table emp2 partition of emp2 for values from (1000) to (5000);

TestDB #create table emp3 partition of emp2 for values from (1000) to (5000);

TestDB #create table emp3 partition of emp2 for values from (5000) to (MAXVALUE);

INSERT INTO emp values (1,'PERMANENT',2000),(2,'CONTRACT',12000),(3,'CO-OP',500) RETURNING*;

#SELECT tableoid::regclass, * from emp;

HASH PARTITION : The benefit of hash partition is that load is evenly distributed or the data is evenly distributed Assume I have C drive and E drive , D drive i have 1 million . Now this 1 million row will be evenly distributed on this three drive.

TestDB # Create table emp(emp_id int, emp_name text, dep_code int ) partition by hash (emp_id);

TestDB # Create table emp_0 partition of emp for values with (MODULUS 3, REMAINDER 0) ;

TestDB # Create table emp_1 partition of emp for values with (MODULUS 3, REMAINDER 1) ;

TestDB # Create table emp_1 partition of emp for values with (MODULUS 3, REMAINDER 2) ;

TestDB # insert into emp select num , 'user_' || num, (RANDOM() * 50)::integer from generate_series(1,1000) as num;

TestDB# select relname,reltuple as rows from pg_class where relname in ('emp','emp_0','emp_1','emp_2');

Partitioning Using Inheritance : I want to Insert data Range from JAN to FEB if i will insert another data it will prompt insert validate date.

Create Table :

#create table bookings(flight_no varchar, flight_name varchar ,booking_date timestamp);

#create table jan_booking(check(booking_date >='2025-01-01' and booking_date <='2025-01-31')) inherits (bookings);

#create table feb_booking(check(booking_date >='2025-02-01' and booking_date <='2025-02-28')) inherits (bookings);

Create Index :

nano=# create index booking_jan_idx on jan_booking using btree(booking_date);

nano=# create index booking_feb_idx on feb_booking using btree(booking_date);

Create Function  : 

create or replace function on_insert ()

returns trigger as

$$

begin

if (new.booking_date > date '2025-01-01' and new.booking_date <= date '2025-01-31') 

then

insert into jan_bookings_values(new.*);

elsif (new.booking_date >= date '2025-02-01' and new.booking_date <=date '2025-02-28') 

then

insert into feb_booking value(new.*);

else

raise exception 'Enter valid booking date';

end if;

return null;

end;

$$ languages  plpgsql;

Create Trigger :

create trigger booking_entry before insert 

on booking 

for each row 

execute procedure on_insert();