extra_window_functions
extra_window_functions
extra_window_functions : Extra Window Functions for PostgreSQL
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 4720 | extra_window_functions
|
extra_window_functions
|
1.0 |
FUNC
|
PostgreSQL
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-r
|
No
|
Yes
|
No
|
Yes
|
yes
|
no
|
| Relationships | |
|---|---|
| See Also | pg_idkit
pgx_ulid
pg_uuidv7
permuteseq
pg_hashids
sequential_uuids
topn
quantile
|
no pg14 on el8/9
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG
|
1.0 |
18
17
16
15
14
|
extra_window_functions |
- |
| RPM | PGDG
|
1.0 |
18
17
16
15
14
|
extra_window_functions_$v |
- |
| DEB | PGDG
|
1.0 |
18
17
16
15
14
|
postgresql-$v-extra-window-functions |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
el8.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
el9.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
MISS
|
el9.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
el10.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
el10.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
d12.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
d12.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
d13.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
d13.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
u22.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
u22.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
u24.x86_64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
u24.aarch64
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
PGDG 1.0
|
Source
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall 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 14Create this extension with:
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;Last updated on