All geek questions in one place

Change database sorting, Ctype type in Postgresql

how can I change Collation, cType is en_IN from en_US.UTF-8

  List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres

my current postgressive version is 8.4 ive installed it with

 sudo apt-get install postgresql-8.4 postgresql-contrib-8.4

im doing this on my ubuntu amazon ec2 server

+10
source share
4 answers

My recommendation:

  • take pg_dumpall

  • reinitialize the db cluster, making sure the local information is correct.

  • restore the dump.

I found that it is sometimes possible to create db with template template0 (-T template0 from bash or WITH TEMPLATE template0 from psql) to use the non-init-db locale.

+8
source

There is no need to recreate the entire database cluster. However, you need to restore your database.

Run createb with these options ( man createdb ):

  -E encoding, --encoding=encoding Specifies the character encoding scheme to be used in this database. The character sets supported by the PostgreSQL server are described in Section 22.3.1, "Supported Character Sets", in the documentation. -l locale, --locale=locale Specifies the locale to be used in this database. This is equivalent to specifying both --lc-collate and --lc-ctype. --lc-collate=locale Specifies the LC_COLLATE setting to be used in this database. --lc-ctype=locale Specifies the LC_CTYPE setting to be used in this database.

It seems you really cannot change the collation of an existing database:

 => ALTER DATABASE dbname SET "Collate" To Russian; ERROR: unrecognized configuration parameter "Collate"

Note that you can set sort options for a table or column; see a good tutorial on sorting options in PostgreSQL.

+4
source

its very very simple solutions.

Step 1.su - postgres Step2. psql Setp3. update the database pg_database encoding = pg_char_to_encoding ('UTF8') where datname = 'icinga'; (do not forget to add;) Step 4. to check

0
source

This worked for me for my DEV database. Do not try to do this for your production database. You may lose existing indexes.

Repeat the query below for template1 and template0

update pg_database set datcollate='en_IN', datctype='UTF-8' where datname='postgres'

-1
source

Source: https://habr.com/ru/post/956142/

More articles:


All Articles