how can i change the "character type" of a database in postgresql?

Question

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

possible duplicate of How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin? — Dietrich Epp
– Dietrich Epp, Commented Sep 24, 2013 at 18:24
@DietrichEpp The question you suggest, is about encoding. I'm asking about collation and character type — slevin
– slevin, Commented Sep 24, 2013 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
– Dietrich Epp, Commented Sep 24, 2013 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
– slevin, Commented Sep 24, 2013 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
– Dietrich Epp, Commented Sep 24, 2013 at 18:40

Ihor Romanchenko · Accepted Answer · 2013-09-24 18:52:52Z

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

edited Sep 24, 2013 at 18:52

answered Sep 24, 2013 at 18:43

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

slevin Over a year ago

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. I still see the wrong characters uploaded in the folder. Is it because of the database?

Ihor Romanchenko Over a year ago

@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.

slevin Over a year ago

@ 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

Ihor Romanchenko Over a year ago

@slevin If your DB already uses utf8 as encoding - then probably your application sends the text with wrong encoding.

slevin Over a year ago

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 if you like. Thanks

Collectives™ on Stack Overflow

how can i change the "character type" of a database in postgresql?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related