[SOLVED] how to test if a postgres partition has been populated or not

Issue

How can I (quickly) test if a postgres partition has any rows in it?

I have a partitioned postgres table ‘TABLE_A’, partitioned by date-range. The name of each individual partition indicates the date-range i.e. TABLE_A_20220101 (1st Jan this year) TABLE_A_20220102 (2nd Jan 2022)

The table includes many years of data, so it includes several thousand individual partitions, each partition contains many millions of rows.

Is there a quick way of testing if a partition has any data in it? There are several solutions I’ve found, but they all involve count(*) and all take ages.

Please note – I’m NOT trying to accurately determine the row-count, just determine if each partition has any rows in it.

Solution

You can use an exists condition:

select exists (select * from partition_name limit 1)

That will return true if partition_name contains at least one row

Answered By – a_horse_with_no_name

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *