extra_window_functions

extra_window_functions : Extra Window Functions for PostgreSQL

Overview

Packages

Source

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install extra_window_functions;		# install via package name, for the active PG version

pig install extra_window_functions -v 18;   # install for PG 18
pig install extra_window_functions -v 17;   # install for PG 17
pig install extra_window_functions -v 16;   # install for PG 16
pig install extra_window_functions -v 15;   # install for PG 15
pig install extra_window_functions -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION extra_window_functions;

Usage

Sources:

• v2.0 README
• Version 2.0 SQL
• 1.0-to-2.0 upgrade SQL

Provides window functions that simulate SQL Standard features not available in PostgreSQL syntax, plus novel functions like flip_flop.

CREATE EXTENSION extra_window_functions;

Functions Simulating SQL Standard

Functions Extending SQL Standard (with default values)

Non-Standard Functions

Examples

-- Equivalent to SQL Standard: NTH_VALUE(x, 3) FROM LAST IGNORE NULLS OVER w
SELECT nth_value_from_last_ignore_nulls(x, 3) OVER w FROM t WINDOW w AS (ORDER BY id);

-- Fill forward: carry last non-null value
SELECT lag_ignore_nulls(val, 1) OVER (ORDER BY ts) FROM measurements;

Version 2.0 and Window Frames

Version 2.0 adds count_ties(), avg_rank(), avg_percent_rank(), group_number(boolean), run_length(anyelement), run_position(anyelement), ema(double precision, double precision), interpolate(double precision) and most_common(anyelement). These functions cover peer groups, runs, smoothing and interpolation. Their frame and ordering rules differ: choose ORDER BY and the window frame deliberately, and inspect the official function reference rather than treating every function as an aggregate.

The lag example reads the previous non-null value; use last_value_ignore_nulls over a frame ending at the current row when the current non-null value must also be retained. Upgrade existing installations with ALTER EXTENSION extra_window_functions UPDATE TO '2.0'. The relocatable C extension does not require preload; installation normally needs a superuser. Upstream supports PostgreSQL 9.6–19. PostgreSQL 19 provides standard IGNORE NULLS for several functions, while FROM LAST and the extra 2.0 functions have their own uses.