collection
Memory optimized data type to be used inside of plpglsql func
Repository
aws/pgcollection
https://github.com/aws/pgcollection
Source
pgcollection-1.1.1.tar.gz
pgcollection-1.1.1.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgcollection | 1.1.1 | TYPE | Apache-2.0 | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3630 | collection | No | Yes | No | Yes | No | Yes | - |
| Related | prefix semver unit pgpdf pglite_fusion md5hash asn1oid roaringbitmap |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 1.1.1 | 1817161514 | pgcollection | - |
| RPM | PIGSTY | 1.1.1 | 1817161514 | pgcollection_$v | - |
| DEB | PIGSTY | 1.1.1 | 1817161514 | postgresql-$v-collection | - |
Build
You can build the RPM / DEB packages for pgcollection using pig build:
pig build pkg pgcollection # build RPM / DEB packages
Install
You can install pgcollection directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pgcollection; # Install for current active PG version
pig ext install -y pgcollection -v 18 # PG 18
pig ext install -y pgcollection -v 17 # PG 17
pig ext install -y pgcollection -v 16 # PG 16
pig ext install -y pgcollection -v 15 # PG 15
pig ext install -y pgcollection -v 14 # PG 14
dnf install -y pgcollection_18 # PG 18
dnf install -y pgcollection_17 # PG 17
dnf install -y pgcollection_16 # PG 16
dnf install -y pgcollection_15 # PG 15
dnf install -y pgcollection_14 # PG 14
apt install -y postgresql-18-collection # PG 18
apt install -y postgresql-17-collection # PG 17
apt install -y postgresql-16-collection # PG 16
apt install -y postgresql-15-collection # PG 15
apt install -y postgresql-14-collection # PG 14
Create Extension:
CREATE EXTENSION collection;
Usage
The collection extension provides two memory-optimized collection data types for use within PL/pgSQL functions.
CREATE EXTENSION collection;
Data Types
collection: Key-value pairs with text keys (max 32,767 chars), stored in creation ordericollection: Key-value pairs with 64-bit integer keys, enabling sparse arrays
Both types support type modifiers to specify element types:
DECLARE
c1 collection('date');
ic1 icollection('int4');
Subscript Access
DO $$
DECLARE t_capital collection;
BEGIN
t_capital['USA'] := 'Washington, D.C.';
t_capital['Japan'] := 'Tokyo';
RAISE NOTICE '%', t_capital['USA']; -- Washington, D.C.
END $$;
Core Functions
| Function | Description |
|---|---|
add(coll, key, value) | Add element |
count(coll) | Element count |
delete(coll, key) | Remove element |
exist(coll, key) | Check key existence |
find(coll, key) | Retrieve value |
first(coll) | Move iterator to start |
last(coll) | Move iterator to end |
next(coll) | Advance iterator |
prev(coll) | Reverse iterator |
key(coll) | Current key |
value(coll) | Current value |
copy(coll) | Create copy |
sort(coll) | Sort by keys |
keys_to_table(coll) | All keys as set |
values_to_table(coll) | All values as set |
to_table(coll) | Keys and values as table |
Iterator Example
DO $$
DECLARE t_capital collection;
BEGIN
t_capital['USA'] := 'Washington, D.C.';
t_capital['United Kingdom'] := 'London';
t_capital['Japan'] := 'Tokyo';
t_capital := first(t_capital);
WHILE NOT isnull(t_capital) LOOP
RAISE NOTICE 'Capital of % is %', key(t_capital), value(t_capital);
t_capital := next(t_capital);
END LOOP;
END $$;
Sparse Arrays (icollection)
icollection supports non-contiguous integer indices and distinguishes between NULL values and uninitialized keys:
DECLARE sparse icollection;
BEGIN
sparse[1] := 'first';
sparse[1000000] := 'millionth'; -- no memory wasted on gaps
END;
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.