pg_map

Overview

Snowflake pg_lake provider. Package 3.5.3; SQL 3.5.

Version

Build

You can build the RPM / DEB packages for pg_lake using pig build:

pig build pkg pg_lake         # build RPM / DEB packages

Install

You can install pg_lake 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 pg_lake;          # Install for current active PG version

pig ext install -y pg_lake -v 18  # PG 18
pig ext install -y pg_lake -v 17  # PG 17
pig ext install -y pg_lake -v 16  # PG 16

dnf install -y pg_lake_18       # PG 18
dnf install -y pg_lake_17       # PG 17
dnf install -y pg_lake_16       # PG 16

apt install -y postgresql-18-pg-lake   # PG 18
apt install -y postgresql-17-pg-lake   # PG 17
apt install -y postgresql-16-pg-lake   # PG 16

Create Extension:

CREATE EXTENSION pg_map;

Usage

Sources:

• Official pg_map README
• Version 3.5 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.

The pg_lake release and package version is 3.5.3; the SQL extension version is 3.5. Keep the libraries and 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.