2

I'm using postgreSQL 9.1

I've set the Collation and the Character Type of the database to Greek_Greece.1253 and I want to change it to utf8

To change the collation I should use this, right?

But how can I change the character type?

Thanks

EDIT

I ment to wright C instead of utf8. I would like to change the Collation and the Character Type to C

slevin
  • 4,166
  • 20
  • 69
  • 129
  • possible duplicate of [How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?](http://stackoverflow.com/questions/380924/how-can-i-change-database-encoding-for-a-postgresql-database-using-sql-or-phppga) – Dietrich Epp Sep 24 '13 at 18:24
  • @DietrichEpp The question you suggest, is about encoding. I'm asking about collation and character type – slevin Sep 24 '13 at 18:26
  • Then you are confused. UTF-8 is not a character type, it is not a collation, UTF-8 is an encoding. Windows 1253 is also an encoding, it is not a character type. – Dietrich Epp Sep 24 '13 at 18:32
  • @DietrichEpp Yes, I'm so sorry. I ment to wright C instead of utf8. Thanks for pointing out. I will edit the question – slevin Sep 24 '13 at 18:36
  • Could you explain what you mean by "character type"? In PostgreSQL, "character type" means `varchar(n)`, `text`, etc. 1253 is not a character type, and neither is C. – Dietrich Epp Sep 24 '13 at 18:40
  • @DietrichEpp He is asking about Collation order (`LC_COLLATE`) and Character classification (`LC_CTYPE`). – Ihor Romanchenko Sep 24 '13 at 18:45
  • @DietrichEpp Yes, what Igor, said. In the pgAdmin, I right click on the name of the database I click "Properties" and then on "Definition" I get the Collation and the Character Type... – slevin Sep 24 '13 at 18:49

1 Answers1

2

You cannot change default collation of an existing database. You need to CREATE DATABASE with the collation you need and then dump/restore your schema and data into it.

If you do not want to recreate the database - you can specify collation for every text collumn in your db.

Here is detailed postgres manual on collations: Collation Support.

First line of this manual page states:

LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.

CREATE DATABASE, pg_dump, pg_restore

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • I used pgAdmin to set the collation fo the table's column to `pg_catalog."C";`. The names store in the database fine. When I save Greek characters, I see Greek in the database. This whole question started because of [this](http://stackoverflow.com/questions/18946772/set-charset-when-saving-files-with-php). I still see the wrong characters uploaded in the folder. Is it because of the database? – slevin Sep 24 '13 at 23:49
  • @slevin The problem you pointed to is not about LC_COLLATE and LC_CTYPE. It is about character encodings (like `utf8`, `Windows 1253` and so on). If you want to work with unicode charactesr - to need to set database encoding to unicode (utf8) and make you application send strings in unicode. – Ihor Romanchenko Sep 25 '13 at 08:21
  • @ The encoding of the database is utf8. The collation and the character set are different (Greek...). I will doublecheck everything...Anyhow, thanks for your time – slevin Sep 25 '13 at 10:12
  • @slevin If your DB already uses `utf8` as encoding - then probably your application sends the text with wrong encoding. – Ihor Romanchenko Sep 25 '13 at 10:18
  • Igor, Thanks for your time. I specified collation for every text column in the database. As for the other problem, I also found a solution. Check [here](http://stackoverflow.com/questions/18946772/set-charset-when-saving-files-with-php) if you like. Thanks – slevin Sep 26 '13 at 18:36