EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Table Partitioning

PostgreSQL Table Partitioning

By Sohel SayyadSohel Sayyad

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.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
PROGRAMMING LANGUAGES Course
502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Certification Course
57+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System - Design & Develop an IOT System
65+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Certification Course
19+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more