DISTINCT is not implemented for window functions

Symptoms

A window function was called with DISTINCT in its argument list, which PostgreSQL does not implement. It raises SQLSTATE 0A000 (feature_not_supported).

• DISTINCT used inside a window function call.
• Window aggregates don’t support DISTINCT.
• Common with count(DISTINCT x) OVER (…).

What the server log shows

ERROR:  DISTINCT is not implemented for window functions

Why PostgreSQL raises this — what the manual says

As Section 3.5 Window Functions explains:

PostgreSQL does not support the DISTINCT keyword inside the argument list of a window function call; remove DISTINCT, or compute the distinct set in a subquery before applying the window function.

When an aggregate is used as a window function (with OVER), the DISTINCT qualifier in its arguments is not supported by the executor, so PostgreSQL reports 0A000.

Common causes

• Writing count(DISTINCT col) OVER (…) or similar.
• Porting SQL from a system that allows distinct window aggregates.

How to fix it

1. Pre-aggregate the distinct values in a subquery/CTE, then apply the window function.
2. Use a self-join or array techniques to count distinct values per partition.
3. Compute distinct counts separately and join the result.

Related & next steps

• SQLSTATE 0A000 — Feature Not Supported (full reference)
• Message: set-returning functions are not allowed in WHERE
• Glossary: Window function

Reference: PostgreSQL 18 Section 3.5 “Window Functions”.