extra_window_functions
Extra Window Functions for PostgreSQL
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
extra_window_functions | 1.0 | FUNC | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 4720 | extra_window_functions | No | Yes | No | Yes | No | Yes | - |
| Related | pg_idkit pgx_ulid pg_uuidv7 permuteseq pg_hashids sequential_uuids topn quantile |
|---|
no pg14 on el8/9
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.0 | 1817161514 | extra_window_functions | - |
| RPM | PGDG | 1.0 | 1817161514 | extra_window_functions_$v | - |
| DEB | PGDG | 1.0 | 1817161514 | postgresql-$v-extra-window-functions | - |
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
extra_window_functions: additional window functions for PostgreSQL
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
| Function | Description |
|---|---|
lag_ignore_nulls(expr [, offset [, default]]) | LAG that skips NULL values |
lead_ignore_nulls(expr [, offset [, default]]) | LEAD that skips NULL values |
first_value_ignore_nulls(expr) | FIRST_VALUE skipping NULLs |
last_value_ignore_nulls(expr) | LAST_VALUE skipping NULLs |
nth_value_from_last(expr, offset) | NTH_VALUE counting from end of frame |
nth_value_ignore_nulls(expr, offset) | NTH_VALUE skipping NULLs |
nth_value_from_last_ignore_nulls(expr, offset) | NTH_VALUE from last, skipping NULLs |
Functions Extending SQL Standard (with default values)
| Function | Description |
|---|---|
first_value_ignore_nulls(expr, default) | FIRST_VALUE with default when out of frame |
last_value_ignore_nulls(expr, default) | LAST_VALUE with default when out of frame |
nth_value_from_last(expr, offset, default) | NTH_VALUE from last with default |
nth_value_ignore_nulls(expr, offset, default) | NTH_VALUE with default, skipping NULLs |
nth_value_from_last_ignore_nulls(expr, offset, default) | Combined from-last, ignore-nulls, with default |
Non-Standard Functions
| Function | Description |
|---|---|
flip_flop(expr [, expr]) | Flip-flop operator: returns false until first expr is true, then true until second expr matches |
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 lead_ignore_nulls(val, 1) OVER (ORDER BY ts) FROM measurements;
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.