pg_pinyin

Overview

optional tokenizer-input overload can integrate with pg_search.

Version

Build

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

pig build pkg pg_pinyin         # build RPM / DEB packages

Install

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

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

dnf install -y pg_pinyin_18       # PG 18
dnf install -y pg_pinyin_17       # PG 17
dnf install -y pg_pinyin_16       # PG 16
dnf install -y pg_pinyin_15       # PG 15
dnf install -y pg_pinyin_14       # PG 14

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

Create Extension:

CREATE EXTENSION pg_pinyin;

Usage

Sources:

• pg_pinyin v0.0.8 README
• pg_pinyin v0.0.8 control file
• 0.0.7 to 0.0.8 upgrade SQL
• 0.0.8 parallel read-only regression
• 0.0.8 implementation

pg_pinyin romanizes Chinese text and exposes tokenizer and query helpers for search applications. Use pg_pinyin to create stable Pinyin search keys, tokenize Han text, or expand Pinyin input into a pg_search regular-expression query.

Version 0.0.8 fixes read-only SPI access for parallel romanization, tokenizer conversion and suffix-dictionary queries. It uses pgrx 0.19.3 and supports PostgreSQL 14-18 plus PostgreSQL 19 beta4. The provided 0.0.7-to-0.0.8 migration makes no SQL object changes; the runtime fix is in the shared library. After installing the new extension files, run ALTER EXTENSION pg_pinyin UPDATE TO ‘0.0.8’. Pigsty package metadata is maintained separately.

Create the Extension

CREATE EXTENSION pg_pinyin;

The extension is relocatable and does not require shared_preload_libraries or a server restart.

Romanize Text

Romanize character by character or use word-aware segmentation:

SELECT pinyin_char_romanize('重庆');
SELECT pinyin_word_romanize('重庆火锅');
SELECT pinyin_word_romanize('重庆火锅', '_custom');

The optional suffix selects custom dictionary tables; it is not an output separator. For example, _custom selects pinyin.pinyin_mapping_custom and pinyin.pinyin_words_custom, whose entries override the built-in dictionaries. Word mode uses dictionary segmentation to resolve contextual pronunciations. Clear the suffix cache after modifying these tables using public.pinyin_clear_suffix_cache(’_custom’).

Use pg_search Tokenizer Input

Word romanization also accepts a pg_search tokenizer result when that extension is available:

SELECT pinyin_word_romanize(
  description::pdb.icu::text[]
)
FROM documents;

The overload returns romanized text; it does not expose a row-per-token API. Use the plain-text overload when pg_search tokenization is not required.

Build a pg_search Query

When pg_search was installed before pg_pinyin, pg_pinyin provides a typed overload that returns pdb.query:

SELECT *
FROM documents
WHERE id @@@ pinyin_regex_phrase(
  'chong qing',
  slope => 1,
  max_expansions => 64,
  generated_pinyin => true
);

If pg_search is absent, the same entry point is installed as an error-reporting stub rather than silently returning a different type. Install dependencies in the intended order and test the function signature after upgrades.

Object Index

• pinyin_char_romanize(text [, suffix]) returns character-based Pinyin text.
• pinyin_word_romanize(text [, suffix]) returns dictionary-segmented Pinyin text.
• pinyin_word_romanize(tokenizer_input [, suffix]) accepts a pg_search tokenizer result.
• pinyin_regex_phrase(text, slope, max_expansions, generated_pinyin) constructs a pg_search Pinyin phrase query when that integration is available.
• pinyin_regex_phrase_patterns is an internal pattern-building helper; prefer the public query function.

Operational Notes

The extension ships generated character and word dictionaries in its pinyin schema. Treat those tables as extension-managed data rather than application tables. Romanization is normalization, not translation, and ambiguous or domain-specific readings may require application-side review.