iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
You can store translated values in PostgreSQL without adding a column for every language, but a schema change alone will not make an existing app display them. Store translations in a jsonb column or a separate translation table, then add a small compatibility layer that selects the requested locale and applies an explicit fallback rule. The goal is to keep most of the application’s existing data-access interface intact—not to make language selection happen automatically.
What “without rewriting your app” can—and cannot—mean
Suppose the application currently runs SELECT name FROM products. Adding a name_i18n column does not change the value that query returns. The application must read the new storage somehow, and some layer must decide which locale to use and what to do when its translation is missing.
A limited change at the query or model boundary can preserve the interface most of the application already uses. Depending on the application, that might be a data-access adapter, a view, or a small resolver. This is an architectural approach, not a transparent translation feature promised by PostgreSQL; the right option depends on how the app queries and writes data.
A generated column is not a general substitute for that resolver. PostgreSQL restricts generated-column expressions to immutable expressions over the current row, and they cannot contain subqueries. They therefore cannot dynamically look up a translation for each request’s locale. See the PostgreSQL 18 generated-columns documentation.
#1 Best Overall
Choose where translated values belong
Two common designs avoid a physical column per language. Neither is a built-in PostgreSQL localization framework; they are schema choices with different trade-offs.
| Consideration | jsonb on the existing row |
Separate translation table |
|---|---|---|
| Shape | One field stores locale-keyed values alongside the product. | One row per product and locale. |
| Read pattern | Convenient when fetching a product and its labels together. | Usually requires a join or a separate lookup. |
| Constraints and workflow | Locale keys and completeness need additional validation or application logic. | Relational keys and constraints can express uniqueness and support workflow fields. |
| Updates | Updating a translation updates and locks the containing row. | Translation rows can be changed independently of the product row. |
| Indexing | GIN can support documented JSONB operators; choose indexes to match actual predicates. | Ordinary relational keys and indexes can support locale-specific lookups. |
| Existing application compatibility | Still requires a resolver or adapter if current reads select only the old field. | Still requires a resolver or adapter, plus retrieval from the translation relation. |
Option 1: Store a locale-keyed JSONB object
For a modest set of translations that is usually read with its parent record, a JSONB object can keep the data together:
ALTER TABLE products ADD COLUMN name_i18n jsonb;An example value is {"en":"Hat","es":"Sombrero","fr-CA":"Chapeau"}. Use standardized locale identifiers and a stable object shape. Define whether a request for fr-CA may fall back to fr, to a default locale, or to the existing name value. Never let incidental JSON key order choose the result.
Recommended Free Tools
Rank #2
PostgreSQL JSONB supports GIN indexing for documented containment, key-existence, and JSONPath operators, but an index helps only when the query uses a compatible predicate. The database does not automatically ensure that keys are valid product locales or that required translations exist; add suitable validation in the application or schema. PostgreSQL also cautions that a JSON document should have a reasonably fixed structure and manageable size, and updating it locks the whole row. See PostgreSQL 18 JSON types and indexing.
Option 2: Store translations in a relation
A translation table makes the product-locale relationship explicit:
CREATE TABLE product_translation (
product_id bigint NOT NULL REFERENCES products(id),
locale text NOT NULL,
name text NOT NULL,
PRIMARY KEY (product_id, locale)
);The primary key prevents duplicate translations for the same product and locale. A relation is often a better fit when translations need independent workflow state, completeness auditing, or relational constraints on locale values. The cost is an extra lookup or join, and it still needs the same locale resolver as JSONB. This is design guidance, not a canonical schema prescribed by PostgreSQL.
Keep locale selection and fallback explicit
Storage does not define what locale a request wants. Resolve the locale before reading the value, and make the fallback policy part of the application’s behavior. A resolver should specify:
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →- How the requested locale is obtained, such as from a user preference or request setting.
- Whether locale tags match exactly, or whether a language-only fallback is permitted.
- The ordered fallback chain, including whether the original-language value is an acceptable final fallback.
- What happens if no usable value exists: show the source text, omit the field, or report a missing translation.
Apply the same policy to every read path that presents translated content. A narrow resolver can be a small change; leaving fallback behavior implicit can create inconsistent results between screens, exports, background jobs, and caches.
Translation storage is not collation or full-text search
Sorting and comparison
Storing a French or Spanish string does not make its sorting or comparison rules language-aware. PostgreSQL supports locale providers including ICU, when available in the build, and libc. ICU can be customized, but results can depend on the ICU version; libc behavior can vary by platform. The PostgreSQL 17 documentation defines a collation as “an SQL schema object that maps an SQL name to locales provided by libraries installed in the operating system.” See PostgreSQL 17 collation support.
Nondeterministic ICU collations can treat byte-distinct strings as equal, which affects comparison and uniqueness behavior. They also have performance and operational trade-offs; PostgreSQL documents that pattern matching is unavailable with such collations. If ordering, equality, or uniqueness matters to the product, test representative names and accents on the PostgreSQL and ICU build you will deploy.
Full-text search
Full-text search has its own language configurations and dictionaries. A translated JSONB value and a locale-aware collation do not automatically provide the right tokenization or stemming. Choose and validate the text-search configuration for each language the product actually searches. See PostgreSQL 18 full-text search.
Free tools Windows power users keep installed
One-click scans. No signup required.
Database localization
PostgreSQL’s localization facilities cover concerns such as locale-specific collation, number formatting, translated server messages, and character-set support or conversion. Those facilities are distinct from translating application content stored in a table. See PostgreSQL 18 localization support.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Add translations without a risky cutover
Keep the existing field’s meaning stable while introducing the new path. Plan for every way data is read and written, not just the main screen.
- Inventory access paths. Find reads and writes for the current field, including ORM-generated SQL, background jobs, exports, and cache keys.
- Add nullable storage. Introduce the JSONB column or translation relation without changing existing reads. For JSONB, the example DDL is
ALTER TABLE products ADD COLUMN name_i18n jsonb;. - Populate translations. Backfill or enter values while preserving the original field. Define and record the intended fallback behavior for missing translations.
- Introduce the resolver. Make the requested locale and ordered fallback rules explicit at the query or model boundary. Track missing translations so gaps are visible.
- Validate queries and constraints. Enforce the locale and uniqueness rules appropriate to the chosen design. For JSONB, add indexes only when the real predicates use supported operators, and inspect query plans.
- Roll out in stages. Verify which reads and writes use the new path before switching all callers. Keep a rollback route until the intended translation path is consistently in use.
Do not assume every ALTER TABLE change has the same operational impact. PostgreSQL documents that lock levels vary by subcommand and that ACCESS EXCLUSIVE is the default unless a subcommand says otherwise. Check the exact operation against the PostgreSQL version and table you will deploy. See PostgreSQL 18 ALTER TABLE.
Choose based on the data and access pattern
Use JSONB when translations are modest, can vary by row, and are commonly fetched with the parent record. Prefer a translation relation when locale-specific rows need stronger relational structure, independent workflow, or straightforward completeness auditing. In either design, preserving an application interface means adapting how that interface resolves values; PostgreSQL storage alone cannot infer the request locale or fallback policy.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
