pg_phone

pg_phone is a PostgreSQL 14–18 extension implemented in Rust with pgrx. It provides a normalized phone_number base type backed by bundled libphonenumber metadata, strict parsing, validation, formatting, B-tree/hash indexes, hash partitioning, bulk and extraction APIs, prefix-search helpers, and user-owned carrier/MCC/MNC catalogs.

The extension never performs network access and does not claim that a valid number is assigned, reachable, SMS-capable, or owned by a particular person or carrier.

Build and install

Prerequisites are Rust 1.88 or newer, PostgreSQL development headers, and cargo-pgrx 0.19.2.

cargo install cargo-pgrx --version 0.19.2 --locked
cargo pgrx init --pg18=/path/to/pg_config
./install.sh --pg-config /path/to/pg_config

install.sh runs cargo pgrx package and includes the 0.1 base schema and the 0.1→1.0→1.1 upgrade chain. Copy the resulting library, control file and SQL files into the corresponding PostgreSQL directories, then run:

CREATE EXTENSION pg_phone;

The PostgreSQL 18 container image can be built with:

docker build -f docker/Dockerfile -t pg-phone:1.1.0 .

Quick start

CREATE TABLE contacts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    raw_phone text,
    phone phone_number UNIQUE
);

INSERT INTO contacts(raw_phone, phone)
VALUES ('067 123 45 67', phone_parse('067 123 45 67', 'UA'));

SELECT phone, phone_region(phone), phone_type(phone), phone_is_valid(phone)
FROM contacts;
-- +380671234567 | UA | mobile | true

Direct casts deliberately require international input:

SELECT '+1 (202) 555-0173'::phone_number; -- accepted
SELECT '2025550173'::phone_number;        -- SQLSTATE 22P02
SELECT phone_parse('2025550173', 'US');   -- accepted
SELECT phone_try_parse('dirty input');    -- NULL

Extensions are canonical and participate in equality:

SELECT '+12025550173;ext=204'::phone_number;
SELECT phone_same_number(
    '+12025550173;ext=100',
    '+12025550173;ext=200'
); -- true

Indexing and search

The default B-tree and hash operator classes support equality, ordering, unique constraints, joins, hash indexes, and hash partition keys.

Prefix-heavy workloads have three expression-index options:

CREATE INDEX contacts_phone_key ON contacts(phone_search_key(phone));
SELECT * FROM contacts
WHERE phone_search_key(phone) >= lower(phone_prefix_range('+38067'))
  AND phone_search_key(phone) <  upper(phone_prefix_range('+38067'));

CREATE INDEX contacts_phone_tokens
ON contacts USING gin(phone_search_tokens(phone));
SELECT * FROM contacts
WHERE phone_search_tokens(phone) @> ARRAY['+38067'];

CREATE INDEX contacts_phone_span
ON contacts USING gist(phone_search_range(phone));
SELECT * FROM contacts
WHERE phone_search_range(phone) <@ phone_prefix_range('+38067');

Bulk processing and extraction

SELECT phone_try_parse_many(
    ARRAY['0671234567', 'bad', NULL],
    'UA'
);

SELECT *
FROM phone_extract(
    'Kyiv +380 67 123 45 67; London +44 20 7946 0018',
    NULL,
    100
);

SELECT *
FROM phone_extract_auto(
    'US (202) 555-0173; GB 020 7946 0018',
    'valid',
    100
);

Extraction offsets are zero-based UTF-8 byte offsets. Input is bounded to 1 MiB and results to 10,000 per call.

User-owned network data

Static phone metadata cannot reliably identify a live carrier because number portability changes ownership. pg_phone therefore queries tables owned and updated by the application:

CREATE TABLE number_ranges (
    prefix text PRIMARY KEY,
    metadata jsonb NOT NULL
);
CREATE TABLE ported_numbers (
    phone phone_number PRIMARY KEY,
    metadata jsonb NOT NULL
);

SELECT phone_network_lookup(
    '+380671234567',
    'number_ranges'::regclass,
    'ported_numbers'::regclass
);

See network catalogs for MCC/MNC and range-index examples.

Compatibility and verification

• SQL API reference
• Stable storage and binary wire format
• Upgrade policy
• PostgreSQL and API support policy
• Security model
• Specification compliance map

Development checks:

cargo fmt --all -- --check
cargo clippy --all-targets -- -D warnings
cargo pgrx test pg18
tests/upgrade/test-upgrade.sh "$(cargo pgrx info pg-config 18)"
cargo check --manifest-path fuzz/Cargo.toml --all-targets

Run SQL and pgbench benchmarks with:

DATABASE_URL=postgres:///postgres bench/run.sh 10000 100000 1000000

Pass 10000000 explicitly for the largest dataset from the specification.