Internationalising a database

Making several languages and countries live in one database: server and client encoding, collation and sort order, case sensitivity, regional formats and time zones.

25 cards
5 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
Scenarios

A few cards from the deck

Server encoding: what is it?
Show the answer
The code that sets how a database's characters are written as bytes on disk. It is chosen when the database is created, most often UTF-8.
A UTF-8 database accepts every alphabet. A Latin-1 database refuses a character it cannot represent, such as “œ” or “€”.
What is the output of the last SELECT?

```sql
CREATE TABLE rdv (quand timestamptz);
INSERT INTO rdv VALUES ('2025-07-01 12:00+00');
SET timezone = 'America/New_York';
SELECT quand FROM rdv;
`
- 2025-07-01 08:00:00-04
- 2025-07-01 12:00:00+00
- 2025-07-01 08:00:00-05
- 2025-07-01 16:00:00-04
Show the answer
2025-07-01 08:00:00-04
The instant 12:00 UTC is displayed in the session's time zone. In July, New York is on summer time, UTC−4: 8:00. With UTC−5 (winter time) it would read 7:00.
In PostgreSQL, a database's encoding and default collation are fixed at its …; ALTER DATABASE cannot change them afterwards.
Show the answer
In PostgreSQL, a database's encoding and default collation are fixed at its creation; ALTER DATABASE cannot change them afterwards.
Changing encoding or collation means a new database, into which the data is reloaded with pg_dump and pg_restore.
Client encoding: what is it?
Show the answer
The code in which a session exchanges its text with the server, which converts on the fly between it and the database's encoding.
It is set per session (SET client_encoding = 'LATIN1'). If it does not tell the truth about the bytes sent, the conversion produces broken accents.

Study this deck