pgbson

Overview

PGXN distribution name is bson; CREATE EXTENSION name is pgbson; package release 2.0.4 still installs extension SQL version 2.0; RPM package root is postgresbson and requires libbson.

Version

Build

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

pig build pkg pgbson         # build RPM / DEB packages

Install

You can install pgbson 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:

• Install
• pig
• dnf
• apt

pig install pgbson;          # Install for current active PG version

pig ext install -y pgbson -v 18  # PG 18
pig ext install -y pgbson -v 17  # PG 17
pig ext install -y pgbson -v 16  # PG 16
pig ext install -y pgbson -v 15  # PG 15
pig ext install -y pgbson -v 14  # PG 14

dnf install -y postgresbson_18       # PG 18
dnf install -y postgresbson_17       # PG 17
dnf install -y postgresbson_16       # PG 16
dnf install -y postgresbson_15       # PG 15
dnf install -y postgresbson_14       # PG 14

apt install -y postgresql-18-pgbson   # PG 18
apt install -y postgresql-17-pgbson   # PG 17
apt install -y postgresql-16-pgbson   # PG 16
apt install -y postgresql-15-pgbson   # PG 15
apt install -y postgresql-14-pgbson   # PG 14

Create Extension:

CREATE EXTENSION pgbson;

Usage

Sources:

• postgresbson README at the 2.0.4 revision
• META.json version 2.0.4
• pgbson control file
• Version 2.0 SQL API

pgbson adds a BSON data type, typed path accessors, JSON-style operators, casts, and expression-index support. Use it when binary BSON must be stored without first converting every value to JSONB, especially when BSON type fidelity or byte-level round trips matter.

The distribution release is 2.0.4 while the extension control and SQL API version remain 2.0.

Create the Extension

CREATE EXTENSION pgbson;

The native module uses libbson. Install a package built against compatible PostgreSQL and libbson versions.

Store and Validate BSON

The bytea-to-bson cast validates input when a value is written. Version 2.0.4 documents that reads can then assume the stored BSON is valid. Do not bypass the type’s input or cast path with unsafe low-level writes.

Extract Values

Typed dot-path accessors avoid materializing every intermediate object:

SELECT bson_get_datetime(payload, 'msg.header.event.ts'),
       bson_get_string(payload, 'data.customer.name')
FROM events;

Use bson_get_bson for a subdocument:

SELECT bson_get_bson(payload, 'msg.header.event')
FROM events;

JSON-style navigation is also available:

SELECT payload->'msg'->'header'->'event'->>'ts'
FROM events;

Function and Operator Index

• bson_get_string, bson_get_int32, bson_get_int64, bson_get_double, bson_get_decimal: typed scalar accessors.
• bson_get_datetime, bson_get_binary, bson_get_boolean: accessors for additional BSON types.
• bson_get_bson: return an embedded BSON document.
• bson_get_jsonb_array: convert an array endpoint to a PostgreSQL jsonb array.
• -> and -»: navigate values with JSON-like syntax.
• bson casts to json and jsonb: expose Extended JSON for PostgreSQL JSON processing.
• bson and bytea casts: preserve the BSON binary representation.

Index and Interoperate

Create expression indexes on frequently queried paths:

CREATE INDEX events_customer_id_idx
ON events (bson_get_string(payload, 'data.customer.id'));

Cast a subdocument to jsonb when PostgreSQL’s JSON operators are more convenient:

SELECT bson_get_bson(payload, 'msg.header')::jsonb ? 'event'
FROM events;

Caveats

• A typed getter returns useful data only when the endpoint has the expected BSON type. Make type expectations explicit in ingestion code.
• bson_get_bson returns NULL for scalar endpoints because a scalar is not a BSON document.
• Dot-path accessors are generally preferable to long operator chains for repeated extraction because they avoid intermediate BSON values.
• BSON and JSONB have different type and ordering semantics. A cast can be useful but is not a lossless replacement for every BSON workflow.