pg_i18n

What it is

A column holds either a plain string or a JSON object of translations, and pg_i18n gives you the functions to work with both at once:

name
-----------------------------------
Chair
{"en": "Chair", "it": "Sedia"}

Tested on PostgreSQL 14, 16 and 17. Needs 9.5 or later. MIT licensed.

Where to use it

pg_i18n fits when translations live inside the row rather than in a separate translations table, and when you cannot or do not want to rewrite the application that reads and writes those columns.

A legacy database with mixed content

Some rows hold plain strings, others hold JSON objects that someone started writing by hand. pg_i18n treats both uniformly, so you can start reading per language today and clean up later with the migration.

An API you cannot change

The API reads and writes the columns as plain strings. Put the view layer in front of the table: the API keeps its queries, the database stores every language, and the language comes from a session setting such as SET i18n.lang = 'it', set per connection, per transaction or per database role.

Product catalogues, CMS content, labels

Names, descriptions, categories, menu entries: short to medium texts that need to exist in a handful of languages and where a machine translation is an acceptable first draft. The automation keeps the configured languages filled and a coverage view shows what a human still has to check.

Multi-tenant or multi-region apps

Each connection or role sets its own language and fallback policy, so the same view serves an Italian tenant and a German one without any change in the application.

Install

As an extension

git clone https://github.com/sirmmo/pg_i18n
cd pg_i18n
make install                 # PG_CONFIG=/path/to/pg_config make install if needed
psql -d mydb -c 'CREATE EXTENSION pg_i18n'

make install only copies a control file and one SQL script into the server's extension directory, so on a host without make you can copy them by hand. CREATE EXTENSION does not need a superuser, only CREATE on the database. To keep the functions in their own schema: CREATE EXTENSION pg_i18n SCHEMA i18n;.

As a plain script

psql -d mydb -f i18n.sql -f i18n_auto.sql     # i18n_auto.sql is optional

Everything is created in the first schema of the current search_path. Re-running the files is safe.

Quick start

SET i18n.default_lang = 'en';       -- fallback language (default: en)
SET i18n.lang = 'it';               -- language for this session

SELECT i18n_get(name) FROM products;
-- 'Chair'                       -> Chair     (plain string, returned as-is)
-- {"en":"Chair","it":"Sedia"}   -> Sedia

UPDATE products SET name = i18n_set(name, 'Sedia rossa') WHERE id = 1;
-- 'Chair' -> {"en": "Chair", "it": "Sedia rossa"}   (plain string promoted to default_lang)

The data model

A translatable value is one of:

• a plain string, taken to be in the default language (i18n.default_lang, en unless set);
• a JSON object whose keys are language codes and whose values are strings. Any text column that happens to contain other JSON, say {"count": 3}, is treated as a plain string;
• NULL.

Language codes are opaque keys. Use en, en-GB, pt-BR as you like, but nothing resolves between en and en-GB on read. An empty-string translation counts as not set everywhere: reads fall through it and the automation fills it.

Every function exists for text and for jsonb columns. PostgreSQL picks the right one from the column type, so the same queries work before and after the migration.

Reading

Writing

i18n_set returns the new value to store. It never writes anything itself, so it composes with any UPDATE or INSERT.

SELECT i18n_set('Chair', 'it', 'Sedia', 'en');                     -- {"en": "Chair", "it": "Sedia"}
SELECT i18n_set('{"en":"Chair"}', 'en', 'Armchair', 'en');         -- {"en": "Armchair"}
SELECT i18n_set('{"en":"Chair","it":"Sedia"}', 'it', NULL, 'en');  -- {"en": "Chair"}   (NULL removes)
SELECT i18n_set(name, 'Sedia');                                    -- session language, session default

A plain string is promoted to {default_lang: value} before the new language is added. Removing the last language yields NULL rather than {}.

Fallback and missing values

By default a language that is not set falls back as far as needed, so the application always gets some text: requested language, then default language, then the first non-empty one. Two session settings change that for the session-driven forms and for the wrapped views:

SET i18n.fallback = 'none';     -- any (default) | default | none
SET i18n.missing  = 'empty';    -- null (default) | empty

SELECT i18n_get('{"en":"Chair","it":"Sedia"}', 'de');
-- fallback any:      Chair
-- fallback default:  Chair
-- fallback none:     NULL, or '' with i18n.missing = 'empty'

A NULL column value stays NULL whatever the settings. For a fixed choice inside one query use the immutable forms, i18n_exact(v, 'de', 'en') or i18n_get(v, 'de', 'en', 'default').

All session settings

These are ordinary custom settings: SET per connection, SET LOCAL per transaction (the right choice behind a transaction-mode pooler such as PgBouncer), or permanently with ALTER ROLE api_user SET i18n.lang = 'it'.

Keeping a string-based application untouched

If the application already reads and writes the columns as plain strings, hide the JSON behind a view:

ALTER TABLE products RENAME TO products_i18n;
SELECT i18n_wrap_table('products_i18n', '{name,description}', 'products');

 application (strings only)          view "products"                 table "products_i18n"
 ─────────────────────────           ────────────────────            ─────────────────────────
 SELECT name FROM products    ──►    i18n_get(name)           ◄──    {"en":"Chair","it":"Sedia"}
 UPDATE products SET name=…   ──►    INSTEAD OF trigger       ──►    i18n_set(name, lang, …)
                                     language = i18n.lang

The application keeps using products and only needs i18n.lang set. What it sees:

• SELECT returns the translation for the session language, following the fallback policy.
• INSERT stores {"<lang>": value}; columns left out keep their defaults.
• UPDATE changes only the current language inside the JSON. A legacy plain string is promoted on the first write. Columns whose visible value did not change are not touched.
• DELETE and RETURNING work as expected.

The table needs a primary key. To remove the layer, drop the view and rename the table back.

Migrating to jsonb

Once every writer goes through the functions or the view, turn the text columns into real jsonb with a constraint, and get containment queries and GIN indexes for free:

BEGIN;
SELECT * FROM i18n_migration_report('products_i18n', 'name');
--  col_type | total | nulls | plain | translated | other_json
--  text     | 12040 |    15 |  9871 |       2154 |          0

DROP VIEW IF EXISTS products;               -- ALTER COLUMN TYPE refuses a dependent view
SELECT i18n_migrate_table('products_i18n', '{name,description}', 'en');
SELECT i18n_wrap_table('products_i18n', '{name,description}', 'products');
COMMIT;

For each column the migration promotes plain strings to {"en": value}, alters the type to jsonb, and adds a CHECK that only accepts a translation object. It takes an ACCESS EXCLUSIVE lock for the rewrite, and drops a text default that would no longer be valid. Inspect other_json first: those values start with { but are not translation objects and would be promoted wholesale.

Searching and indexes

-- one language, with fallback
SELECT * FROM products_i18n WHERE i18n_get(name, 'it', 'en') ILIKE '%sedia%';
-- any language
SELECT * FROM products_i18n WHERE i18n_all(name) ILIKE '%chair%';

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products_i18n USING gin (i18n_get(name, 'it', 'en') gin_trgm_ops);
CREATE INDEX ON products_i18n USING gin (i18n_all(name) gin_trgm_ops);

-- jsonb only
CREATE INDEX ON products_i18n USING gin (name);
SELECT * FROM products_i18n WHERE name @> '{"it": "Sedia"}';
SELECT * FROM products_i18n WHERE NOT name ? 'de';          -- rows missing German

Do not LIKE the raw column: on JSON rows it also matches keys, quotes and \uXXXX escapes. Filters written against the wrapped view use the session language and cannot use these indexes; query the base table with the explicit form when speed matters.

Filling missing languages automatically

PostgreSQL cannot call HTTP APIs portably, so the work is split. The database detects rows missing a configured language and queues them; a worker outside the database calls the provider and writes back. Everything the worker does goes through queue functions, so it needs no knowledge of your tables.

 INSERT/UPDATE ──► AFTER trigger ──► i18n_queue (pending) ──► NOTIFY
                                          │
      worker: i18n_queue_claim() ◄────────┘
              provider.translate()         DeepL · Google · OpenRouter · echo
              i18n_queue_complete() ──► i18n_fill(col, translations)   only adds what is still missing

SELECT i18n_auto_enable('products_i18n', 'name', '{en,it,de}',
                        NULL,           -- source language: NULL = default lang, else first available
                        'openrouter',   -- provider: NULL = worker default
                        'furniture product names, keep brand names untranslated');

SELECT i18n_backfill('products_i18n', 'name');    -- queue every existing row that misses a language
SELECT i18n_auto_disable('products_i18n', 'name');

Rules that keep it safe and cheap:

• Only missing or empty languages are requested, and only what is still missing at write-back time is written. A human translation entered while a job is in flight wins.
• A changed source text does not retranslate languages that already exist. Clear a language with i18n_set(v, 'it', NULL) to have it redone.
• One open job per row and column; repeated writes update it. Claims use FOR UPDATE SKIP LOCKED, so several workers can run.
• Jobs go pending → processing → done or error after the configured attempts; stale jobs from a dead worker are requeued.

The worker

A single Python file depending on psycopg 3 and the standard library, also available as a container.

cd worker && pip install -r requirements.txt
export PG_I18N_DSN=postgresql://user:pw@host/db
export PG_I18N_PROVIDER=deepl DEEPL_API_KEY=...             # or
export PG_I18N_PROVIDER=google GOOGLE_TRANSLATE_API_KEY=... # or
export PG_I18N_PROVIDER=openrouter OPENROUTER_API_KEY=... OPENROUTER_MODEL=anthropic/claude-sonnet-4.5

./pg_i18n_worker.py            # runs forever: LISTEN/NOTIFY plus a periodic poll
./pg_i18n_worker.py --once     # drain the queue and exit, for cron

docker build -t pg_i18n-worker worker/ && docker run --env-file .env pg_i18n-worker

To add another service, write a class with a translate(text, source_lang, target_langs, hint, detect) method returning ({lang: text}, detected_lang) and register it in PROVIDERS.

Language detection

An application that knows nothing about languages inserts plain strings, taken to be in the default language. Someone using the API in an Italian session may paste an English text, which lands under it. Detection, off by default, fixes both at translation time:

SELECT i18n_auto_enable('notes', 'body', '{en,it,de}', NULL, 'deepl', NULL, true);

The worker asks the provider (or the local langdetect package) what language the source text is in. If it differs from the language the text was stored under, the text is moved to the detected key, the wrong key is cleared, and every configured language, including the wrong one, is filled from the detected text. A language outside the configured set stays under its own key:

'Bonjour'

{"en": "Hello", "fr": "Bonjour", "it": "Ciao"}

The move happens only while the original key still holds exactly the inserted text and the detected key is empty, so a human edit in the meantime wins. Provider codes are mapped onto yours (EN matches en, zh-CN matches zh) and regional variants of the same language never trigger a move.

Checking coverage

SELECT * FROM i18n_coverage;
--      tbl       | col  | enabled | lang | total | missing | done_pct
--  products_i18n | name | t       | de   |     5 |       5 |      0.0
--  products_i18n | name | t       | en   |     5 |       2 |     60.0
--  products_i18n | name | t       | it   |     5 |       0 |    100.0

SELECT * FROM i18n_missing_translations WHERE NOT queued;
--      tbl       | col  | enabled |    pk     | present | missing | queued
--  products_i18n | name | t       | {"id": 6} | {it}    | {de,en} | f

SELECT status, count(*) FROM i18n_queue GROUP BY 1;
UPDATE i18n_queue SET status = 'pending', attempts = 0 WHERE status = 'error';   -- retry

Both views cover every column configured for automation. For an unconfigured column, or to limit the scan to one table, call i18n_missing_rows(table, col, langs) and i18n_coverage_of(table, col, langs) directly.

Deployment notes

• Connection poolers. With PgBouncer in transaction mode, set the language with SET LOCAL inside each transaction, or fix it per role with ALTER ROLE.
• Permissions. The worker needs to execute the i18n_queue_* functions and update the target tables. Nothing else.
• Backups. Installed as an extension, the i18n_auto and i18n_queue tables are included by pg_dump.
• Upgrades. Re-running the SQL files, or ALTER EXTENSION pg_i18n UPDATE once versions are published, is safe: everything is CREATE OR REPLACE and new columns are added conditionally.
• Costs. DeepL and Google make one request per target language and row; OpenRouter one per row. Only missing languages are ever requested.

FAQ

Does the application have to change at all?

No, if you use the view layer. It has to set i18n.lang, which can be done per role on the database side without touching application code.

What happens to a plain string when someone writes Italian to it?

It becomes {"en": "old text", "it": "new text"}: the plain string is promoted to the default language first.

Can I use it without the automation?

Yes. i18n.sql alone gives you reading, writing, the view layer, the migration and the search helpers. The automation is a second file, and the worker is optional on top of that.

Will the automation overwrite human translations?

No. It only writes languages that are still missing at write-back time. The single exception is language detection, which moves a text to its detected key while that text is still exactly what was inserted.

Can I run it on a managed PostgreSQL where I cannot install extensions?

Yes. Load the SQL files with psql; they need no superuser and no server-side files.