How to select from explicit partition in PostgreSQL

Question

In Oracle, MySQL I can select from partition

SELECT ... FROM ... PARTITION (...)

In SQL Server syntax is a bit different involving partitioning function.

is there a way to do it in PostgreSQL?

thank you!

SELECT * FROM product where tableoid::regclass::text ='product_a' — Alex B
– Alex B, Commented Nov 8, 2014 at 16:13
SELECT t.* FROM product t JOIN pg_class c ON c.oid = t.tableoid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE t.tableoid=16411 — Alex B
– Alex B, Commented Nov 8, 2014 at 16:14

Daniel Vérité · Accepted Answer · 2014-11-08 21:04:23Z

4

PostgreSQL provided partitioning through table inheritance.

Partitions are child tables with an unique name like any other table, so you just select from them directly with their names. The only special case is for the parent table: to select data from the parent table ignoring the child tables, an additional keyword ONLY is used as in SELECT * FROM ONLY parent_table.

Example from the manual:

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

So select * from measurement_y2006m02 would get data from only this partition.

answered Nov 8, 2014 at 21:04

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex B Over a year ago

Thank you, I can actually use it.

Matteo Over a year ago

is there some way to call the parent table?

Collectives™ on Stack Overflow

How to select from explicit partition in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related