How to Query Partitioned Tables in PostgreSQL: Syntax and Examples

In PostgreSQL, you query a partitioned table just like a regular table using SELECT. The database automatically routes your query to the relevant partitions based on the partition key, so you do not need to specify partitions manually.

Syntax

To query a partitioned table, use the standard SELECT statement on the parent table. PostgreSQL handles the partition routing internally.

• SELECT: Retrieves data.
• FROM partitioned_table: The main table that is partitioned.
• WHERE: Optional filter to limit rows, which helps PostgreSQL prune partitions.

sql

SELECT column1, column2 FROM partitioned_table WHERE partition_key = 'value';

Example

This example shows how to create a partitioned table by range, insert data, and query it. The query on the parent table returns data from the correct partitions automatically.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

INSERT INTO sales (sale_date, amount) VALUES
  ('2023-06-15', 100),
  ('2024-03-20', 200);

SELECT * FROM sales WHERE sale_date >= '2024-01-01';

Output

 id | sale_date  | amount 
----+------------+--------
  2 | 2024-03-20 |    200
(1 row)

Common Pitfalls

Common mistakes when querying partitioned tables include:

• Querying partitions directly instead of the parent table, which defeats partitioning benefits.
• Not using the partition key in WHERE clauses, which can cause full scans of all partitions.
• Assuming partitions are separate tables; always query the parent table.

sql

/* Wrong: querying a child partition directly */
SELECT * FROM sales_2023 WHERE amount > 50;

/* Right: query the parent table to use partition pruning */
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND amount > 50;

Quick Reference

Action	Description
Query parent table	Use SELECT on the main partitioned table, not child partitions.
Use partition key in WHERE	Helps PostgreSQL skip irrelevant partitions.
Avoid querying partitions directly	Limits flexibility and partition pruning.
Partition types	Range, List, and Hash are supported in PostgreSQL.
Check partitions	Use \d+ parent_table in psql to see partitions.

Key Takeaways

Always query the parent partitioned table using SELECT.

Include the partition key in WHERE clauses for efficient queries.

Avoid querying child partitions directly to benefit from partition pruning.

PostgreSQL automatically routes queries to relevant partitions.

Use \d+ in psql to inspect partitioned tables and their partitions.