aggs_for_vecs

Aggregate functions for array inputs

FUNC

PIGSTY

aggs_for_vecs : Aggregate functions for array inputs

Overview

ID	Extension	Package	Version	Category	License	Language
4740	aggs_for_vecs	aggs_for_vecs	`1.4.1`	FUNC	MIT	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d-r	No	Yes	No	Yes	yes	no

Relationships
See Also	intarray arraymath floatvec aggs_for_arrays

Packages

Type	Repo	Version	PG Major Compatibility	Package Pattern	Dependencies
EXT	PIGSTY	`1.4.1`	18 17 16 15 14	`aggs_for_vecs`	-
RPM	PIGSTY	`1.4.1`	18 17 16 15 14	`aggs_for_vecs_$v`	-
DEB	PIGSTY	`1.4.1`	18 17 16 15 14	`postgresql-$v-aggs-for-vecs`	-

Linux / PG	PG18	PG17	PG16	PG15	PG14
el8.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
el8.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
el9.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
el9.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
el10.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
el10.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
d12.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
d12.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
d13.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
d13.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u22.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u22.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u24.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u24.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u26.x86_64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1
u26.aarch64	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1	PIGSTY 1.4.1

Package	Version	OS	ORG	SIZE	File URL
`aggs_for_vecs_18`	`1.4.1`	el8.x86_64	pigsty	43.9 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el8.x86_64.rpm
`aggs_for_vecs_18`	`1.4.1`	el8.aarch64	pigsty	43.9 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el8.aarch64.rpm
`aggs_for_vecs_18`	`1.4.1`	el9.x86_64	pigsty	42.4 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el9.x86_64.rpm
`aggs_for_vecs_18`	`1.4.1`	el9.aarch64	pigsty	43.4 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el9.aarch64.rpm
`aggs_for_vecs_18`	`1.4.1`	el10.x86_64	pigsty	42.4 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el10.x86_64.rpm
`aggs_for_vecs_18`	`1.4.1`	el10.aarch64	pigsty	43.7 KiB	aggs_for_vecs_18-1.4.1-1PIGSTY.el10.aarch64.rpm
`postgresql-18-aggs-for-vecs`	`1.4.1`	d12.x86_64	pigsty	82.1 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_amd64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	d12.aarch64	pigsty	82.2 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_arm64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	d13.x86_64	pigsty	81.9 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~trixie_amd64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	d13.aarch64	pigsty	82.2 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~trixie_arm64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u22.x86_64	pigsty	88.6 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~jammy_amd64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u22.aarch64	pigsty	89.2 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~jammy_arm64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u24.x86_64	pigsty	84.7 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~noble_amd64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u24.aarch64	pigsty	86.4 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~noble_arm64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u26.x86_64	pigsty	85.8 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~resolute_amd64.deb
`postgresql-18-aggs-for-vecs`	`1.4.1`	u26.aarch64	pigsty	87.9 KiB	postgresql-18-aggs-for-vecs_1.4.1-1PIGSTY~resolute_arm64.deb

Package	Version	OS	ORG	SIZE	File URL
`aggs_for_vecs_17`	`1.4.1`	el8.x86_64	pigsty	43.9 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el8.x86_64.rpm
`aggs_for_vecs_17`	`1.4.1`	el8.aarch64	pigsty	43.9 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el8.aarch64.rpm
`aggs_for_vecs_17`	`1.4.1`	el9.x86_64	pigsty	42.6 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el9.x86_64.rpm
`aggs_for_vecs_17`	`1.4.1`	el9.aarch64	pigsty	43.5 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el9.aarch64.rpm
`aggs_for_vecs_17`	`1.4.1`	el10.x86_64	pigsty	42.3 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el10.x86_64.rpm
`aggs_for_vecs_17`	`1.4.1`	el10.aarch64	pigsty	43.8 KiB	aggs_for_vecs_17-1.4.1-1PIGSTY.el10.aarch64.rpm
`postgresql-17-aggs-for-vecs`	`1.4.1`	d12.x86_64	pigsty	82.3 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_amd64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	d12.aarch64	pigsty	82.2 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_arm64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	d13.x86_64	pigsty	81.7 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~trixie_amd64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	d13.aarch64	pigsty	82.3 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~trixie_arm64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u22.x86_64	pigsty	93.0 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~jammy_amd64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u22.aarch64	pigsty	93.7 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~jammy_arm64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u24.x86_64	pigsty	84.7 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~noble_amd64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u24.aarch64	pigsty	86.4 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~noble_arm64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u26.x86_64	pigsty	85.8 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~resolute_amd64.deb
`postgresql-17-aggs-for-vecs`	`1.4.1`	u26.aarch64	pigsty	88.1 KiB	postgresql-17-aggs-for-vecs_1.4.1-1PIGSTY~resolute_arm64.deb

Package	Version	OS	ORG	SIZE	File URL
`aggs_for_vecs_16`	`1.4.1`	el8.x86_64	pigsty	43.8 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el8.x86_64.rpm
`aggs_for_vecs_16`	`1.4.1`	el8.aarch64	pigsty	43.9 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el8.aarch64.rpm
`aggs_for_vecs_16`	`1.4.1`	el9.x86_64	pigsty	42.6 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el9.x86_64.rpm
`aggs_for_vecs_16`	`1.4.1`	el9.aarch64	pigsty	43.5 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el9.aarch64.rpm
`aggs_for_vecs_16`	`1.4.1`	el10.x86_64	pigsty	42.3 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el10.x86_64.rpm
`aggs_for_vecs_16`	`1.4.1`	el10.aarch64	pigsty	43.8 KiB	aggs_for_vecs_16-1.4.1-1PIGSTY.el10.aarch64.rpm
`postgresql-16-aggs-for-vecs`	`1.4.1`	d12.x86_64	pigsty	82.3 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_amd64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	d12.aarch64	pigsty	82.2 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_arm64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	d13.x86_64	pigsty	81.7 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~trixie_amd64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	d13.aarch64	pigsty	82.2 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~trixie_arm64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u22.x86_64	pigsty	93.0 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~jammy_amd64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u22.aarch64	pigsty	93.7 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~jammy_arm64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u24.x86_64	pigsty	85.0 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~noble_amd64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u24.aarch64	pigsty	86.4 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~noble_arm64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u26.x86_64	pigsty	85.7 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~resolute_amd64.deb
`postgresql-16-aggs-for-vecs`	`1.4.1`	u26.aarch64	pigsty	88.1 KiB	postgresql-16-aggs-for-vecs_1.4.1-1PIGSTY~resolute_arm64.deb

Package	Version	OS	ORG	SIZE	File URL
`aggs_for_vecs_15`	`1.4.1`	el8.x86_64	pigsty	44.0 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el8.x86_64.rpm
`aggs_for_vecs_15`	`1.4.1`	el8.aarch64	pigsty	43.7 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el8.aarch64.rpm
`aggs_for_vecs_15`	`1.4.1`	el9.x86_64	pigsty	42.9 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el9.x86_64.rpm
`aggs_for_vecs_15`	`1.4.1`	el9.aarch64	pigsty	43.5 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el9.aarch64.rpm
`aggs_for_vecs_15`	`1.4.1`	el10.x86_64	pigsty	42.6 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el10.x86_64.rpm
`aggs_for_vecs_15`	`1.4.1`	el10.aarch64	pigsty	43.8 KiB	aggs_for_vecs_15-1.4.1-1PIGSTY.el10.aarch64.rpm
`postgresql-15-aggs-for-vecs`	`1.4.1`	d12.x86_64	pigsty	82.5 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_amd64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	d12.aarch64	pigsty	82.2 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_arm64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	d13.x86_64	pigsty	82.1 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~trixie_amd64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	d13.aarch64	pigsty	82.3 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~trixie_arm64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u22.x86_64	pigsty	93.2 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~jammy_amd64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u22.aarch64	pigsty	93.5 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~jammy_arm64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u24.x86_64	pigsty	85.3 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~noble_amd64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u24.aarch64	pigsty	86.5 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~noble_arm64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u26.x86_64	pigsty	86.2 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~resolute_amd64.deb
`postgresql-15-aggs-for-vecs`	`1.4.1`	u26.aarch64	pigsty	88.1 KiB	postgresql-15-aggs-for-vecs_1.4.1-1PIGSTY~resolute_arm64.deb

Package	Version	OS	ORG	SIZE	File URL
`aggs_for_vecs_14`	`1.4.1`	el8.x86_64	pigsty	44.0 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el8.x86_64.rpm
`aggs_for_vecs_14`	`1.4.1`	el8.aarch64	pigsty	43.7 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el8.aarch64.rpm
`aggs_for_vecs_14`	`1.4.1`	el9.x86_64	pigsty	42.8 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el9.x86_64.rpm
`aggs_for_vecs_14`	`1.4.1`	el9.aarch64	pigsty	43.5 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el9.aarch64.rpm
`aggs_for_vecs_14`	`1.4.1`	el10.x86_64	pigsty	42.6 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el10.x86_64.rpm
`aggs_for_vecs_14`	`1.4.1`	el10.aarch64	pigsty	43.8 KiB	aggs_for_vecs_14-1.4.1-1PIGSTY.el10.aarch64.rpm
`postgresql-14-aggs-for-vecs`	`1.4.1`	d12.x86_64	pigsty	82.3 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_amd64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	d12.aarch64	pigsty	82.5 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~bookworm_arm64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	d13.x86_64	pigsty	81.9 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~trixie_amd64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	d13.aarch64	pigsty	82.1 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~trixie_arm64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u22.x86_64	pigsty	93.0 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~jammy_amd64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u22.aarch64	pigsty	93.5 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~jammy_arm64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u24.x86_64	pigsty	85.2 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~noble_amd64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u24.aarch64	pigsty	86.3 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~noble_arm64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u26.x86_64	pigsty	86.0 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~resolute_amd64.deb
`postgresql-14-aggs-for-vecs`	`1.4.1`	u26.aarch64	pigsty	87.9 KiB	postgresql-14-aggs-for-vecs_1.4.1-1PIGSTY~resolute_arm64.deb

Source

Repository

github.com/pjungwir/aggs_for_vecs

Source Tarball

aggs_for_vecs-1.4.1.tar.gz

pig build pkg aggs_for_vecs;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

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

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

Create this extension with:

CREATE EXTENSION aggs_for_vecs;

Usage

aggs_for_vecs: aggregate functions for arrays (vector/row-based)

Provides aggregate functions that operate on arrays as vectors, computing position-wise statistics across multiple rows. Supports SMALLINT, INTEGER, BIGINT, REAL, and DOUBLE PRECISION.

CREATE EXTENSION aggs_for_vecs;

Aggregate Functions

Function	Description
`vec_to_count(anyarray)`	Count of non-nulls in each position
`vec_to_sum(anyarray)`	Sum in each position
`vec_to_min(anyarray)`	Minimum in each position
`vec_to_max(anyarray)`	Maximum in each position
`vec_to_mean(anyarray)`	Average in each position (returns `FLOAT[]`)
`vec_to_weighted_mean(values, weights)`	Weighted average in each position
`vec_to_var_samp(anyarray)`	Sample variance in each position
`vec_to_first(anyarray)`	First non-null in each position (use with ORDER BY)
`vec_to_last(anyarray)`	Last non-null in each position (use with ORDER BY)
`hist_2d(x, y, ...)`	2-D histogram
`hist_md(vals, indexes, ...)`	N-dimensional histogram

Non-Aggregate Functions

Function	Description
`vec_add(l, r)`	Element-wise addition
`vec_sub(l, r)`	Element-wise subtraction
`vec_mul(l, r)`	Element-wise multiplication
`vec_div(l, r)`	Element-wise division
`vec_elements(array, indexes)`	Select elements at given indexes
`pad_vec(array, length)`	Extend array to given length with NULLs
`vec_coalesce(array, default)`	Replace NULLs with default
`vec_trim_scale(numeric[])`	Trim trailing zeros from NUMERIC elements
`vec_without_outliers(vals, mins, maxs)`	Replace outliers with NULL

Examples

-- Position-wise minimum across rows
SELECT vec_to_min(vals) FROM (VALUES
  (ARRAY[1,2,3,4]),
  (ARRAY[5,0,-5,0]),
  (ARRAY[3,6,0,9])
) AS t(vals);
-- {1,0,-5,0}

-- Position-wise average
SELECT vec_to_mean(vals) FROM my_table;