skip to content

In PostgreSQL, how do a database's server encoding and a session's client_encoding differ, and what happens to bytes that do not fit?

level: juniorimportance: should knowfreq 40%

answer

  1. one fixed, one per session
  2. conversion happens on the server
  3. 22021 versus 22P05
  4. SQL_ASCII validates nothing

basics

~20 s

The server encoding, normally UTF8, is fixed per database at creation; client_encoding is a per-session setting the server converts to and from. Invalid input bytes fail with SQLSTATE 22021, and characters the other encoding cannot hold fail with 22P05.

solid answer

~40 s

Each PostgreSQL database stores text in one server encoding, chosen at `CREATE DATABASE` (defaulting from `initdb`, normally `UTF8`) and shown read-only as `server_encoding`; it cannot be changed in place, only by creating a new database from `template0` and reloading. `client_encoding` belongs to the session: it defaults to the database encoding and can be changed with `SET client_encoding`, `PGCLIENTENCODING`, libpq's `client_encoding=auto` or psql's `\encoding`. The server converts every string between the two. Bytes that are illegal in the declared encoding are rejected with `invalid byte sequence for encoding "UTF8"` (SQLSTATE 22021), and a character with no equivalent in the target encoding raises 22P05; nothing is silently replaced. The trap is a `SQL_ASCII` server encoding, which neither converts nor validates, so bytes from differently encoded clients pile up indistinguishably.

code

sql · 6 lines
sql
SHOW server_encoding;            -- UTF8
SET client_encoding TO 'LATIN1';
SELECT headword FROM headwords WHERE lang = 'ja';
-- ERROR:  character with byte sequence 0xe8 0xbe 0x9e in encoding "UTF8"
--         has no equivalent in encoding "LATIN1"
RESET client_encoding;

go deeper

for a junior

Recall that UTF8 is the server encoding of choice, that client_encoding is set per session, and that bad bytes raise an error instead of being stored.

for a middle

Explain where each encoding is set, why a new encoding means a new database copied from template0, and how SQLSTATE 22021 differs from 22P05.

for a senior

Show that you can trace 22021 errors or garbled text to a client misdeclaring its encoding, and that you would refuse SQL_ASCII for a multilingual production database.

for a principal

Weigh migrating a legacy SQL_ASCII or LATIN1 database to UTF8: cleansing mixed bytes, the reload window, and which clients must fix their declared encoding first.

## Two encodings, two scopes PostgreSQL keeps two ideas apart. The **server encoding** is how text bytes are stored inside one database. The **client encoding** is how one session wants to send and receive text. The server converts between them on every round trip, so a client never has to know how the data is stored. | | server encoding | `client_encoding` | |---|---|---| | Scope | one database | one session | | Chosen by | `initdb` (template default) and `CREATE DATABASE ... ENCODING` | the client, per connection | | How to see it | `SHOW server_encoding;` or psql's `\l` | `SHOW client_encoding;` | | Can it change? | not in place: it is read-only and cannot be set in `postgresql.conf` | at any time, with `SET` or `RESET` | | Typical value | `UTF8` | `UTF8`, or a legacy encoding such as `LATIN1` | `initdb` derives the default encoding from the locale unless you pass `-E`/`--encoding`. Later databases inherit from their template, and a different encoding can only be chosen when copying `template0`: copying any other database keeps its encoding and locale, because changing them could corrupt data already stored there. `ALTER DATABASE` has no encoding option, so converting an existing database means creating a new one and reloading the data. ## How a session picks its client encoding Unless something sets it, `client_encoding` equals the database encoding. The ways to set it are: 1. `SET client_encoding TO 'LATIN1';`, or the standard spelling `SET NAMES 'LATIN1';`. 2. psql's `\encoding LATIN1` meta-command. 3. The `PGCLIENTENCODING` environment variable, read when the connection opens. 4. libpq's `client_encoding` connection parameter, which also accepts `auto` to derive the encoding from the client's `LC_CTYPE`. 5. A configured default for the parameter, for example `ALTER ROLE ... SET client_encoding`. Methods 3 to 5 take effect when the connection opens, and `SET` or `\encoding` can override them later in the session; `RESET client_encoding` returns to the session's default. ## What happens to bytes that do not fit PostgreSQL validates text instead of storing whatever arrives: - **Invalid input bytes.** A sequence that is not legal in the declared encoding fails with `invalid byte sequence for encoding "UTF8": 0x...`, SQLSTATE **22021** (`character_not_in_repertoire`). The usual cause is a client sending LATIN1 or Windows-1252 bytes while declaring UTF8. - **Unconvertible characters.** When the server converts and the target encoding lacks the character, the statement fails with `character with byte sequence ... in encoding "UTF8" has no equivalent in encoding "LATIN1"`, SQLSTATE **22P05** (`untranslatable_character`). No question marks are substituted. - **No conversion path.** Conversions exist only for the encoding pairs PostgreSQL ships in `pg_conversion`, so not every client encoding works with every server encoding. ## SQL_ASCII: a declaration of ignorance `SQL_ASCII` behaves unlike every other encoding. Bytes 0-127 are read as ASCII and bytes 128-255 are stored uninterpreted, with **no validation and no conversion** in either direction. The manual calls it a declaration of ignorance about the encoding. A database that receives UTF-8 from one application and LATIN1 from another stores both, and nothing can tell them apart afterwards. It is easy to get by accident: with the libc provider, `initdb --no-locale` (or a `C`/`POSIX` locale) defaults to `SQL_ASCII`, while the ICU provider picks `UTF8`. Pass `--encoding=UTF8` explicitly. On the client side, a `client_encoding` of `SQL_ASCII` disables conversion, but a server whose encoding is not `SQL_ASCII` still validates what arrives, so the net effect is as if the client had declared the server's encoding. ## Diagnosing a rejected or garbled headword - Check `SHOW client_encoding;` from the failing application's own connection, not from psql on your laptop, which declares its terminal's encoding. - A 22021 error on insert points at the client: it sends bytes in one encoding while declaring another. - Text that is stored without error but displays as `é` instead of `é` usually means UTF-8 bytes were declared as LATIN1 on the way in, so the server converted them a second time. The stored value itself is now wrong and needs repair, not a display fix. - `convert_from(bytea, encoding)` and `convert_to(text, encoding)` let you inspect or repair individual values in SQL. ## Encoding and locale must agree - A libc locale such as `de_DE.UTF-8` works correctly with exactly one encoding, and on most modern systems PostgreSQL enforces the matching pair. - ICU locales work with most, but not all, server encodings. - The builtin provider's `C.UTF-8` and `PG_UNICODE_FAST` locales require `UTF8`, and so does the `casefold()` function. For a multilingual dictionary app, that leaves `UTF8` as the only sensible server encoding: German, Japanese and Greek headwords live in the same column, and each client converts at its own edge by declaring its real encoding.

  • Why does CREATE DATABASE with a different ENCODING have to name TEMPLATE template0?
    A new database is a copy of its template. Copying `template1` or any other database keeps that database's encoding and locale, because objects already stored there could be corrupted by reinterpreting their bytes. `template0` is the pristine template, so it is the one copy where a different encoding and locale may be chosen.
  • If the server encoding is UTF8 and a client declares client_encoding SQL_ASCII, is its input still checked?
    Yes. A `SQL_ASCII` client encoding switches conversion off, but a server whose own encoding is not `SQL_ASCII` still validates incoming text for that encoding. Illegal UTF-8 bytes are still rejected with SQLSTATE 22021; the net effect is as though the client had declared `UTF8`.

  • Changing client_encoding converts the data already stored on disk.
  • ALTER DATABASE can switch an existing database from LATIN1 to UTF8 in place.
  • SQL_ASCII is the neutral, safe choice for multilingual text.
  • Characters that cannot be converted are silently replaced with a question mark.
  • server_encoding can be set in postgresql.conf for the whole cluster.