pg_pinyin
Pinyin romanization and search helpers for PostgreSQL
Repository
aiyou178/pg_pinyin
https://github.com/aiyou178/pg_pinyin
Source
pg_pinyin-0.0.2.tar.gz
pg_pinyin-0.0.2.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_pinyin | 0.0.2 | FTS | MIT | Rust |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2190 | pg_pinyin | No | Yes | No | Yes | Yes | Yes | pinyin |
| Related | zhparser pg_search pg_trgm pg_bigm pgroonga pgroonga_database pg_tokenizer fuzzystrmatch |
|---|
pgrx 0.17.0; optional tokenizer-input overload can integrate with pg_search
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 0.0.2 | 1817161514 | pg_pinyin | - |
| RPM | PIGSTY | 0.0.2 | 1817161514 | pg_pinyin_$v | - |
| DEB | PIGSTY | 0.0.2 | 1817161514 | postgresql-$v-pinyin | - |
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
pg_pinyin: Pinyin romanization and search helpers for PostgreSQL
Convert Chinese characters to Pinyin romanization for search and indexing. Works well with pg_trgm for fuzzy Pinyin search or pg_search for word-based search.
CREATE EXTENSION pg_pinyin;
Functions
| Function | Description |
|---|---|
pinyin_char_romanize(text) | Character-level Pinyin romanization |
pinyin_char_romanize(text, suffix) | With custom dictionary suffix |
pinyin_word_romanize(text) | Word-level Pinyin romanization |
pinyin_word_romanize(text, suffix) | With custom dictionary suffix |
Generated Column + Trigram Search
CREATE EXTENSION IF NOT EXISTS pg_pinyin;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE voice (
id bigserial PRIMARY KEY,
description text NOT NULL,
pinyin text GENERATED ALWAYS AS (public.pinyin_char_romanize(description)) STORED
);
CREATE INDEX voice_pinyin_trgm_idx ON voice USING gin (pinyin gin_trgm_ops);
INSERT INTO voice (description) VALUES ('郑爽ABC');
SELECT id, description, pinyin FROM voice;
Custom Dictionary
Provide custom dictionary tables in schema pinyin with a suffix:
CREATE TABLE IF NOT EXISTS pinyin.pinyin_mapping_suffix1 (
character text PRIMARY KEY,
pinyin text NOT NULL
);
CREATE TABLE IF NOT EXISTS pinyin.pinyin_words_suffix1 (
word text PRIMARY KEY,
pinyin text NOT NULL
);
INSERT INTO pinyin.pinyin_mapping_suffix1 (character, pinyin)
VALUES ('郑', '|zhengx|')
ON CONFLICT (character) DO UPDATE SET pinyin = EXCLUDED.pinyin;
-- Use custom dictionary
SELECT public.pinyin_char_romanize('郑爽ABC', '_suffix1');
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.