Partition Queries

To retrieve data in a specified partition.

-- Create a range partitioned table.
m_db=# CREATE TABLE test_range1(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (400),
    PARTITION p3 VALUES LESS THAN (600),
    PARTITION p4 VALUES LESS THAN (800),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
-- Insert 1000 data records.
m_db=# INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');

-- Query the number of data records in the p1 partition.
m_db=# SELECT COUNT(*) FROM test_range1 PARTITION (p1);
 count 
-------
   199
(1 row)

-- Drop.
m_db=# DROP TABLE test_range1;