Skip to content

pg_map

Map type for PostgreSQL, bundled as a required pg_lake component.

pg_lake : Map type for PostgreSQL, bundled as a required pg_lake component.

Overview

ID Extension Package Version Category License Language
2563 pg_map pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--s-d-- No Yes No Yes no no

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake -
RPM PIGSTY 3.7.0 19 18 17 16 15 pg_lake_$v -
DEB PIGSTY 3.7.0 19 18 17 16 15 postgresql-$v-pg-lake -
Linux / PG PG19 PG18 PG17 PG16 PG15
el8.x86_64 N/A N/A N/A N/A N/A
el8.aarch64 N/A N/A N/A N/A N/A
el9.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el9.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A

Source

github.com/Snowflake-Labs/pg_lake/tree/main/pg_map

pig build pkg pg_lake;		# 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 pg_lake;		# install via package name, for the active PG version
pig install pg_map;		# install by extension name, for the current active PG version

pig install pg_map -v 19;   # install for PG 19
pig install pg_map -v 18;   # install for PG 18
pig install pg_map -v 17;   # install for PG 17
pig install pg_map -v 16;   # install for PG 16

Create this extension with:

CREATE EXTENSION pg_map;

Usage

Sources:

pg_map generates strongly typed key/value map domains from PostgreSQL types. A generated map is an array of composite key/value pairs, with type-specific extraction, cardinality, entry, and operator functions. It is used by pg_lake for nested data but can also be used directly.

The reviewed upstream release is 3.6.0, with SQL extension version 3.6 and PostgreSQL 16–19 support. Existing packaged versions are tracked separately. Keep libraries and the query server from the same release.

Create and Use a Map Type

CREATE EXTENSION pg_map;

-- Requires a privileged role; PUBLIC execution is revoked.
SELECT map_type.create('text', 'integer');
-- map_type.key_text_val_int

Construct a value and read it through generated functions or the -> operator:

SELECT map_type.extract(
    '{"(me,1)","(myself,2)","(i,3)"}'::map_type.key_text_val_int,
    'i'
);
-- 3

SELECT
    '{"(me,1)","(myself,2)","(i,3)"}'::map_type.key_text_val_int
    -> 'myself';
-- 2

SELECT key, value
FROM map_type.entries(
    '{"(me,1)","(myself,2)","(i,3)"}'::map_type.key_text_val_int
);

Generated API

  • map_type.create(keytype regtype, valtype regtype, typname text default null): idempotently creates or returns a map type for a key/value pair.
  • map_type.extract(map, key) and generated map -> key: return the value for a key.
  • map_type.cardinality(map): returns the number of entries.
  • map_type.entries(map): expands a map to key, value rows.
  • Generated names normally follow map_type.key_<keytype>_val_<valuetype>; supply typname when a controlled name is required.

Type and Lifecycle Caveats

  • Array types cannot be used as map keys. Array values and nested generated map types are supported.
  • A call to map_type.create creates PostgreSQL types, functions, and operators. Treat it as schema DDL and run it in migrations rather than per-request code.
  • Generated objects are registered as dependencies of pg_map; dropping the extension can remove them and columns that depend on them when CASCADE is used.
  • Map values use PostgreSQL composite-array syntax. Duplicate-key and ordering semantics should be tested for the application’s chosen construction path rather than assumed from JSON objects.
  • The 3.4 to 3.5 map SQL migration is empty; the generated map API remains unchanged.

Upstream 3.6.0

Upstream 3.6.0 adds PostgreSQL 19 support and uses DuckDB 1.5.6. Deploy matching libraries and pgduck_server, keep pg_extension_base preloaded, restart loaded processes and apply the supplied SQL updates to version 3.6. This component’s 3.5-to-3.6 SQL migration is empty; the matching runtime and its dependencies still need the coordinated release upgrade.

Upstream 3.7.0

The current reviewed release is 3.7.0 and this component’s control version is 3.7. Earlier version sections describe historical behavior and package examples. Upgrade the complete family with matching libraries and pgduck_server, restart processes holding old code, and apply the supplied extension updates to SQL version 3.7. This does not establish package availability.

Table reader