5. Localization / Internationalization

5.1. Do I need to specify --enable-locale to get locale support when building PostgreSQL?

Beginning with PostgreSQL 7.3, locale support is enabled by default. Whether it is enabled in a particular database cluster depends on how initdb was carried out.

5.2. Why are my locale settings causing errors?

  • You called initdb with a particular locale, but initdb produced errors like this: Failed to initialize lc_monetary to ''
  • When starting the postmaster (e.g. with pg_ctl) it died with messages such as the above and / or: FATAL: invalid value for option 'LC_MESSAGES': 'de_DE'

You probably specified a locale not supported by your system, even though it looks right and / or worked perfectly on a similar operating system.

For example, initdb -D /path/to/data --locale=de_DE may work on SuSE Linux but not on RedHat or FreeBSD.

Examine the system's locale directory to see exactly which locale names are supported.


5.3. How do I convert an accented (or whatever) character to its ASCII equivalent?

You have a character with an accent or umlaut, such as ö, and wish to knock the funny bits off the top.

Use to_ascii, e.g.

SELECT to_ascii('ö', 'LATIN1')

should return o.

Note 1: currently only the encodings LATIN1, LATIN2 and WIN1250 are supported by to_ascii().

Note 2: If your data makes heavy use of extended ASCII character sets such as LATIN1 (ISO-8859-1), consider using locale settings to perform sorting operations etc.

5.4. How do I ensure correct sort order in a particular language, e.g. German?

The following statement produces results in the wrong order:

test=# SELECT lastname FROM people WHERE lastname LIKE 'M%' ORDER BY lastname;

i.e. März should really appear between Mayer and Mosel

To provide sorting order in a particular language, PostgreSQL must be set up to use a locale appropriate for that language, e.g. de_DE for German or fr_FR for French. The locale for a particular database cluster is defined when initdb is carried out. Specifically the LC_COLLATE must be set correctly. By default it is taken from the environment of the user executing initdb, but can also be explicitly specified using initdb's --locale=LOCALE or --lc-collate=LOCALE flags.

Note that in current versions of PostgreSQL, LC_COLLATE is fixed and cannot be changed without reloading the database into a correctly initialised cluster.

Also note that the character set of a particular database is not directly connected with its locale settings.

i want to insert my sql table in postgres

please reply me soon
Posted by: santhosh | 2007-07-30 06:56