Partitioning in PostgreSQL Database

 Partitioning in PostgreSQL Database

Partition move in List and range form

Create original Table 

db1=# create table bookings(flightno varchar(100), flightname varchar(100), booking_date timestamp);

CREATE TABLE

db1=#

db1=#

db1=# \d bookings

                           Table "public.bookings"

    Column    |            Type             | Collation | Nullable | Default

--------------+-----------------------------+-----------+----------+---------

 flightno     | character varying(100)      |           |          |

 flightname   | character varying(100)      |           |          |

 booking_date | timestamp without time zone |           |          |

Partition table creation

db1=#

db1=# create table jan_bookings(check(booking_date >='2024-01-01' and booking_date<='2024-01-31')) inherits(bookings);

CREATE TABLE

db1=#

db1=#

db1=# create table feb_bookings(check(booking_date >='2024-02-01' and booking_date<='2024-02-29')) inherits(bookings);

CREATE TABLE

db1=#

db1=#

db1=# \dt

            List of relations

 Schema |     Name     | Type  |  Owner

--------+--------------+-------+----------

 public | bookings     | table | postgres

 public | feb_bookings | table | postgres

 public | jan_bookings | table | postgres

(3 rows)

db1=# \d bookings;

                           Table "public.bookings"

    Column    |            Type             | Collation | Nullable | Default

--------------+-----------------------------+-----------+----------+---------

 flightno     | character varying(100)      |           |          |

 flightname   | character varying(100)      |           |          |

 booking_date | timestamp without time zone |           |          |

Number of child tables: 2 (Use \d+ to list them.)

db1=# \d+ bookings;

                                                      Table "public.bookings"

    Column    |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description

--------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------

 flightno     | character varying(100)      |           |          |         | extended |             |              |

 flightname   | character varying(100)      |           |          |         | extended |             |              |

 booking_date | timestamp without time zone |           |          |         | plain    |             |              |

Child tables: feb_bookings,

              jan_bookings

Access method: heap

db1=# \dt

            List of relations

 Schema |     Name     | Type  |  Owner

--------+--------------+-------+----------

 public | bookings     | table | postgres

 public | feb_bookings | table | postgres

 public | jan_bookings | table | postgres

(3 rows)

db1=# \dt jan_bookings

            List of relations

 Schema |     Name     | Type  |  Owner

--------+--------------+-------+----------

 public | jan_bookings | table | postgres

(1 row)

db1=# \d jan_bookings

                         Table "public.jan_bookings"

    Column    |            Type             | Collation | Nullable | Default

--------------+-----------------------------+-----------+----------+---------

 flightno     | character varying(100)      |           |          |

 flightname   | character varying(100)      |           |          |

 booking_date | timestamp without time zone |           |          |

Check constraints:

    "jan_bookings_booking_date_check" CHECK (booking_date >= '2024-01-01 00:00:00'::timestamp without time zone AND booking_date <= '2024-01-31 00:00:00'::timestamp without time zone)

Inherits: bookings

Create index on Partition table

db1=# create index jan_idx on jan_bookings using btree (booking_date);

CREATE INDEX

db1=# create index feb_idx on feb_bookings using btree (booking_date);

CREATE INDEX

db1=#

db1=#

db1=# \di

                 List of relations

 Schema |  Name   | Type  |  Owner   |    Table

--------+---------+-------+----------+--------------

 public | feb_idx | index | postgres | feb_bookings

 public | jan_idx | index | postgres | jan_bookings

(2 rows)

db1=# \d+

                                       List of relations

 Schema |     Name     | Type  |  Owner   | Persistence | Access method |  Size   | Description

--------+--------------+-------+----------+-------------+---------------+---------+-------------

 public | bookings     | table | postgres | permanent   | heap          | 0 bytes |

 public | feb_bookings | table | postgres | permanent   | heap          | 0 bytes |

 public | jan_bookings | table | postgres | permanent   | heap          | 0 bytes |

(3 rows)

Creation of Trigger to call on_insert function

db1=# create trigger booking_entry before insert on bookings for each row execute procedure on_insert();

CREATE TRIGGER

Creation of Function to insert values

db1=#

db1=# create or replace function on_insert() returns trigger as $$

db1$# begin

db1$#   if(new.booking_date >= date '2024-01-01' and new.booking_date <=date '2024-01-31') then

db1$#      insert into jan_bookings values(new.*);

db1$#     elsif (new.booking_date >= date '2024-02-01' and new.booking_date <=date '2024-02-29') then

db1$#      insert into feb_bookings values(new.*);

db1$#     else

db1$#      raise exception 'Enter valid booking date';

db1$#     end if;

db1$#

db1$#     return null;

db1$# end;

db1$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

db1=# \df

                         List of functions

 Schema |   Name    | Result data type | Argument data types | Type

--------+-----------+------------------+---------------------+------

 public | on_insert | trigger          |                     | func

(1 row)

Inserting values in original table

db1=#

db1=# insert into bookings values('dxb102','emirates','2024-02-09');

INSERT 0 0

db1=# insert into bookings values('dxb103','emirates','2024-02-15');

INSERT 0 0

db1=# insert into bookings values('auh345','etihad','2024-01-10');

INSERT 0 0

db1=#

db1=#

db1=#

Checking actual values in tables 

db1=# \dt

            List of relations

 Schema |     Name     | Type  |  Owner

--------+--------------+-------+----------

 public | bookings     | table | postgres

 public | feb_bookings | table | postgres

 public | jan_bookings | table | postgres

(3 rows)

 flightno | flightname |    booking_date

----------+------------+---------------------

 auh345   | etihad     | 2024-01-10 00:00:00

 dxb102   | emirates   | 2024-02-09 00:00:00

 dxb103   | emirates   | 2024-02-15 00:00:00

(3 rows)

db1=# select * from jan_bookings;

 flightno | flightname |    booking_date

----------+------------+---------------------

 auh345   | etihad     | 2024-01-10 00:00:00

(1 row)

db1=#

db1=#

db1=# select * from feb_bookings;

 flightno | flightname |    booking_date

----------+------------+---------------------

 dxb102   | emirates   | 2024-02-09 00:00:00

 dxb103   | emirates   | 2024-02-15 00:00:00

(2 rows)

db1=#