pg_map

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

Overview

Packages

Source

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 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:

• Official pg_map README
• Version 3.4 control file
• Base SQL definition
• Official extension tests and examples

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.

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.
• Version 3.4 changes no map SQL API relative to 3.3.