current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 22 down vote favorite
5

How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?

share|improve this question
up vote 38 down vote accepted

You have to perform:

  1. database dump,
  2. delete database,
  3. create database with new encoding,
  4. import data from dump created in point 1.

Changing it on-the-fly isn't possible as it would require rewriting most of internal database data which is almost equal to recreating db way I described.

share|improve this answer
1  
+1 I came here to say this. – Paul Tomblin Dec 19 '08 at 13:24
    
i have a cpanel account and if i backup the database i will be able to restore only the content and not to recreate database with new encoding – daniels Dec 19 '08 at 13:24
    
drop = delete, here's link to cpanel docs: cpanel.net/support/docs/11/cpanel/databases_delete_post.html – romke Dec 19 '08 at 13:35
1  
PostgreSQL newbie here... How can you do this using SQL and/or the psql command-line tool? For example if the database and server is currently set to SQL_ASCII, but I want it to be UTF-8? – Svish Feb 15 '11 at 12:24
3  
newbie or not, you must know your tools, use pg_dump for database dumping, psql -f for importing previously dumped data. delete/create database you can use many different tools, sql (DROP DATABASE/CREATE DATABASE [postgresql.org/docs/9.0/static/sql-createdatabase.html]), phpPgAdmin, pgAdmin (visual tools). READ tool documentation before performing any action. – romke Feb 17 '11 at 20:42
up vote 10 down vote

You can change encoding on the fly without dump/restore:

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name'
share|improve this answer
    
Now this is a useful answer. "Create database with new encoding" is an incredibly unhelpful instruction when the very question is "How do I change the encoding on a database?" – Bobort Aug 20 '15 at 18:14
    
Is this documented anywhere in postgres manual? playing with postgresql internals and just updating reference informations may lead to serious damages. If this indeed rewrite all indexes and necessary data it is great! But if not you may end up with restoring from your backups. – romke Dec 13 '15 at 18:27
    
@Bobort, you are right.. but there are some caveats on changing "on the fly" stackoverflow.com/a/5091083/903998.. it appears that the right choice is to start with a fresh db (postgres) – Victor yesterday
up vote 3 down vote

To expand on the answers given, you can use these commands to accomplish your task.

// Backup the database to outfile
pg_dump dbname > outfile

// Terminate all connections to the database
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='dbname';"

// Delete the database
psql -c "DROP DATABASE dbname;"

//Re-create the database using the encoding keyword
psql -c "CREATE DATABASE dbname ENCODING='UTF8';"

//Import the saved data
psql -f outfile

Dumping database: http://www.postgresql.org/docs/9.4/static/backup-dump.html

Creating database: http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html

This is a list of all encodings available for version 9.4: http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

share|improve this answer
    
Thanks for detailed instructions. Saved me a lot of typing. – dtoux Dec 4 '15 at 3:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.