CODEWITHSUNDEEP
postgresqlutf-8character-encoding
B Seven
B Seven

Reputation: 45951

How to handle multiple human languages in a PostgreSQL database?

If the database uses UTF-8 encoding, can text from all human languages be properly stored and retrieved?

Are there any "gotchas" when dealing with non-English languages in a PostgreSQL database?

Working in Ruby on Rails and PostgreSQL 9.1.

Upvotes: 3

Views: 1366

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 325201

In addition to Spidey and Kevin's points (use utf-8 in the client and an ENCODING 'utf-8' database, beware of differing collations), I strongly recommend tagging each text field with the language it is in if at all possible.

If you ever want to use full text search or any kind of linguistic analysis, it really helps to know which language each field is in. Full text search can't do root-word analysis etc unless it has a dictionary and suffix list for the text being indexed - and for that it needs to know the language.

Storing ISO 639 language codes is probably a reasonable choice.

Upvotes: 4

kgrittn
kgrittn

Reputation: 19511

Different languages tend to order the same character strings differently, so be careful about the COLLATION when sorting.

http://www.postgresql.org/docs/current/static/collation.html

Upvotes: 3

Spidey
Spidey

Reputation: 2589

UTF-8 can encode all Unicode codepoints, so yes, you won't have any problem at all. You'll need to connect with a UTF-8 connection though, and make sure your application also reads the output as UTF-8 encoded text.

Upvotes: 2

Related Questions