EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Table Partitioning
 

PostgreSQL Table Partitioning

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 22, 2023

PostgreSQL Table Partitioning

 

 

Introduction to PostgreSQL Table Partitioning

PostgreSQL Table Partitioning means we have one largest PostgreSQL table, and this table is splitting into various tables. Partitions can also be foreign tables. The PostgreSQL allows us to partition the table into different ranges where we need to ensure that the values within the different partitions should not overlap. It is necessary to define the list of key values that we should add to each partition. Also, the set of columns used for creating a different partition or the split tables should be similar to the parent. We can perform partitioning in PostgreSQL using different methods, such as range partitioning or list partitioning.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Given below is the syntax:

CREATE TABLE parent
(
Id int,
col_a varchar,
col_b varchar
);
CREATE TABLE range1() inherits (parent);
CREATE TABLE range2() inherits (parent);
CREATE TABLE range3() inherits (parent);

Explanation:

  • The main table, divided into various partitions, is considered a partitioned table.
  • Each partition has the partition key, which is expressions or a list of columns.

How does Table Partitioning work in PostgreSQL?

The PostgreSQL table partition defines how to divide a table into different pieces termed as partitions. The partitions should be created very carefully as it might lead to affect the execution performance of various queries. The PostgreSQL does not allow us to convert the regular or normal table into the partitioned table. Also, we cannot convert the partitioned tables into regular or normal tables. If the constraint is present in the parent table then the PostgreSQL does not allow us to drop the NOT NULL constraint on a partition’s table columns.

The table is partitioned by clearly listing which key values display in each partition. When inserting data into a partitioned table in PostgreSQL, the system assigns the records to one of the partitions based on the partition key. Each partition has its own boundaries, which restrict the data insertions and allows the partition to have data within the boundaries only. The partitioned table does not have any data directly.

The PostgreSQL supports the following types of partitioning:

  • Range Partitioning: PostgreSQL allows us to partition the main table into different ranges, the range is defined by some set of columns or a key column, and it needs to be ensured that the values present in the various ranges should not overlap.
  • List Partitioning: In the case of List partitioning, the main table gets divided into different partitions, which are created by different key values.

Examples of PostgreSQL Table Partitioning

Given below are the examples mentioned :

Let’s create a table named ‘stock_shop’.

Example #1

We create a stock_shop table in PostgreSQL using the CREATE TABLE statement, which includes the PARTITION BY RANGE clause. The table stock_shop will have three columns.

  • stock_date: Its data type is the date and having a constraint, not null.
  • product_name: Its data type is text.
  • qyt: Its data type is an integer.

Code:

CREATE TABLE stock_shop (
stock_date       date not null,
product_name     text,
qyt           integer
) PARTITION BY RANGE (stock_date);

Output:

PostgreSQL Table Partitiong 1

In the above example, we are creating a stock_shop table with Partition BY RANGE for the stock_date column. This will sort all records using the stock_date range.

Example #2

Now there is creating a partition of the stock_shop table using this query.

Code:

CREATE TABLE stock_1 PARTITION OF stock_shop   FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
CREATE TABLE stock_2 PARTITION OF stock_shop   FOR VALUES FROM ('2019-04-01') TO ('2019-05-01');

Output:

PostgreSQL Table Partitiong 2

In this example, we have created two partitions by a range of stock_date columns.

We have created two partitioned named as ‘stock_1’ and ‘stock_2’.

  • Partition ‘stock_1’: The stock_1 will have values stored within the date range (‘2019-03-01’) to (‘2019-04-01’).
  • Partition ‘stock_2’: The stock_2 will have values stored within the date range (‘2019-04-01’) to (‘2019-05-01’).

Example #3

Inserting some data in the Stock_shop table.

Now we will insert some record values in the stock_shop table by using the following INSERT INTO SQL statement.

Code:

INSERT INTO stock_shop
(stock_date, product_name, qyt)
VALUES
('2019-03-02', 'shirt', 65),
('2019-03-03', 't-shirt',  3),
('2019-04-02', 'jeans', 69),
('2019-04-03', 'watches',  2),
('2019-03-02', 'shoes', 70),
('2019-04-03', 'goggles', 98),
('2019-04-02', 'perfume',  17),
('2019-04-01', 'sando',  82);

Output:

Data output

Example #4

Show the content of the table.

Illustrate the content of the table named ‘stock_shop’ by using the following SQL statement and snapshot.

Code:

SELECT * FROM stock_shop WHERE stock_date BETWEEN '2019-02-01' and '2019-03-31';

Output:

Data output

Now we can see, in the table named ‘stock_shop,’ there are some records between 2019-02-01 and 2019-03-31, and only three rows available between 2019-02-01 and 2019-03-31 date range.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Table Partitioning” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Materialized Views
  2. PostgreSQL UNIQUE Index
  3. Array in PostgreSQL
  4. PostgreSQL REINDEX

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW