extra_window_functions

Overview

Version

Install

You can install extra_window_functions directly. First, make sure the PGDG repository is added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install extra_window_functions;          # Install for current active PG version

pig ext install -y extra_window_functions -v 18  # PG 18
pig ext install -y extra_window_functions -v 17  # PG 17
pig ext install -y extra_window_functions -v 16  # PG 16
pig ext install -y extra_window_functions -v 15  # PG 15
pig ext install -y extra_window_functions -v 14  # PG 14

dnf install -y extra_window_functions_18       # PG 18
dnf install -y extra_window_functions_17       # PG 17
dnf install -y extra_window_functions_16       # PG 16
dnf install -y extra_window_functions_15       # PG 15
dnf install -y extra_window_functions_14       # PG 14

apt install -y postgresql-18-extra-window-functions   # PG 18
apt install -y postgresql-17-extra-window-functions   # PG 17
apt install -y postgresql-16-extra-window-functions   # PG 16
apt install -y postgresql-15-extra-window-functions   # PG 15
apt install -y postgresql-14-extra-window-functions   # PG 14

Create Extension:

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.