Postgresql – Counting using window function with partition over an empty table

partitioningpostgresqlwindow functions

If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?

select count(*) over (partition by field_name) 
from my_empty_table

Right now the postgres doesn't emmit any results as the table is empty.

Best Answer

Your query outputs one row for each row in the table, so an empty table must result in an empty output. (COALESCE handles NULL values, but what you have is not a NULL value, but no value at all.)

You could add another query:

select count(*) over (partition by field_name) 
from my_empty_table

union all

select 0
from my_empty_table
where not exists (select * from my_empty_table);

Related Solutions

Sql-server – Best practices for selecting partition boundary ranges to maintain empty partitions at both end

Let me start by mentioning that these 4 boundaries result in following 5 partitions. Partitions 1 and 5 are unbounded one one side and partitions 2, 3, and 4 are constrained to quarterly data:

partition 1: values less than '2014-04-01'
partition 2: values >= '2014-04-01' and < '2014-07-01'
partition 3: values >= '2014-07-01' and < '2014-10-01'
partition 4: values >= '2014-10-01' and < '2015-01-01'
partition 5: values >= '2015-01-01'

The rationale for keeping (at least) the first and last partitions empty is to ensure expensive data movement is not required during partition maintenance of an incremental or sliding window scenario. If data must be moved to a different partition to accommodate new boundaries during MERGE or SPLIT, the logging is about 4 times greater than normal DDL operations. This is especially nasty with large tables, as is common with table partitioning.

Data movement is never required when an existing empty partition is split or when 2 adjacent empty partitions are merged. This is why the best practice is to plan for such, making the maintenance a no-brainer that can easily be automated.

That said, one can split a non-empty partition without overhead in some cases. For example, you could split the function to add the new '2015-04-01' boundary without data movement as long no existing rows are >= '2015-04-01'. But to also avoid an expensive scan during the offline operation, you need an index with the partitioning column as the high-order key column so that SQL Server can efficiently validate no data exists beyond the new boundary. So in your case, you could probably split the last partition in advance of the start of the next quarter without incurring overhead if the partitioning column is indexed (a common design pattern).

Assuming your objective is 4 quarterly partitions of historical data plus the current active quarter in a sliding window, I suggest bounded partitions for each of these 5 quarters plus another bounded one for the future quarter. This will result in 8 partitions when you include the unbounded first and last ones.

CREATE PARTITION FUNCTION PfDateRange(date)
AS RANGE RIGHT FOR VALUES(
      '2014-01-01'
    , '2014-04-01'
    , '2014-07-01'
    , '2014-10-01'
    , '2015-01-01'
    , '2015-04-01'
    , '2015-07-01');

The reason one might want additional future empty partitions is to provide time to react in case partition maintenance is missed for some reason. Multiple future partitions are more important with more frequent maintenance (e.g. daily), and less so for quarterly maintenance.

After the start of the '2015-04-01' quarter, the partition maintenance script can:

switch '2014-01-01' to a and empty staging table and truncate staging
merge the '2014-01-01' partition to remove it
spilt at '2015-10-01' for the next future quarter

You may want to do a sanity check before the maintenance in case you have bad data outside of the expected range (less than '2014-01-01' or greater than '2015-07-01'). Alternatively, maintain a check constraint for the expected range so that only valid data can be inserted.

Postgresql – postgres window functions: using CASE to partition by age group

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM 
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
                 THEN '0-15' 
                 ELSE '15+' 
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs 
    ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = FALSE 
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT 
        cu.*, 
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
            THEN '0-15' 
            ELSE '15+' 
        END AS ageGroup
    FROM 
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs 
        ON cu.competition_user_id = ccs.competition_user_id 
    WHERE cu.left_competition = FALSE 
      AND cu.competition_id = :compId
  ) AS d

Best Answer

Related Solutions

Sql-server – Best practices for selecting partition boundary ranges to maintain empty partitions at both end

Postgresql – postgres window functions: using CASE to partition by age group

Related Question