Today, My problem is wrong character with Postgres

I must maintance a website that i dont know

Using primary key is character, but user input data by SHIFT-JIS. So when executeSQL ---> Bum

I suggest add new column to indentify that data.

MySQL versus PostgreSQL: Adding an Auto-Increment Column to a Table

Posted in Databases and Programming on Wednesday, the 5th of March, 2008.

The bulk of my database experience (almost eight years now) has been with the popular open-source MySQL database management system. MySQL has progressed significantly over the years, and has grown into a remarkable product. It finally has all the must-have features such as views, stored procs and referential integrity, coupled with the blistering performance for which MySQL has always been known. In short, it rocks.

But I digress. I've recently been having to get to grips with PostgreSQL (an old version of course - 7.1 or so - just to make life really interesting). It's largely intuitive, but there are quirks around most corners. This is my favourite so far: I recently needed to add an auto-incrementing integer "id" column to a table.
MySQL

This sort of thing will be second nature to MySQL developers:
ALTER TABLE mytable ADD myid INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

One SQL command - not bad.
PostgreSQL

It turned out not to be so easy with our Postgres installation. For a start, there are no auto_increment columns. So, as with several other major RDBMSs, the solution is to create a "sequence", which is kind of like a pseudo-table which acts as a counter:
CREATE SEQUENCE mytable_myid_seq;

Next, we have to add our new column to the table, and specify that for each new row it should take its value from the sequence, using the NEXTVAL() function. For reasons best known to the Postgres guys, you can't do this in one step:
ALTER TABLE mytable ADD myid INT UNIQUE;

And then:
ALTER TABLE mytable ALTER COLUMN myid SET DEFAULT NEXTVAL('mytable_myid_seq');

We're getting there. We now have an auto increment column. The problem is that Postgres won't backfill this with values automatically: all pre-existing rows are currently null for this column. Let's change that:
UPDATE mytable SET myid = NEXTVAL('mytable_myid_seq');

Job done. Well, some time later, the job will be done. That final step is one of the slowest things you can possibly ask Postgres to do. For a mid-sized table (around 5,000,000 rows, with a handful of small numeric and text columns), that took about 2.5 hours on powerful hardware - so you'll want to leave this for a quiet time. Fortunately Postgres treats the UPDATE as an atomic transaction: nothing is committed until the command completes, so it will be difficult for you to leave the data in an inconsistent state.

TIP Convert latin1 to UTF-8 in MySQL

Today my problem is convert database from latin1 to utf-8 (with data is Shift-jis)

- Create new database, one table, input a lot fot record to test

Step by step:

Since MySQL 4.1, UTF-8 is the default charset. If you have an old database, containing data encoded in latin1, and you want upgrade to a newer MySQL server, than you have to do the following:

MySQL dump

First of all, we need to dump the old data into a file.

Code: Create a MySQL dump

$ mysqldump -h host.com --user=frog -p --default-character-set=latin1 -c \ --insert-ignore --skip-set-charset dbname > dump.sql

Please mention, that you have to replace the user, the host and the dbname, otherwise it will result in an error :)

Convert dump

Next thing to do is, converting the characters in the MySQL dump from latin1 to UTF-8

Code: Convert dump

$ iconv -f ISO-8859-1 -t UTF-8 dump.sql > dump_utf8.sql $ perl -pi -w -e 's/CHARSET=latin1/CHARSET=utf8/g;' dump_utf8.sql

If you have another source charset, you need to replace the -f option with your local character set.

Drop and create

Now it's time to drop the old database and create a new one with UTF-8 support.

Code: Drop and Create

$ mysql --user=frog -p --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"

(MySql seems to recommend utf8_unicode_ci over utf8_general_ci for 5.1+, see http://dev.mysql.com/doc/refman/5.1/de/charset-unicode-sets.html)

Import dump to databse

Last but not least, we need to import the converted data back to the new database.

Code: Import dump

$ mysql --user=frog --max_allowed_packet=16M -p --default-character-set=utf8 dbname <>

The max_allowed_packet-option is sometimes important. If your import ends up with a "ERROR 1153 at line 42: Got a packet bigger than 'max_allowed_packet'", you need to increase the packet size. Please mention, that you also need to update /etc/mysql/my.cnf and set max_allowed_packet=16M under the [mysqld] directive

Done!!!Everything is okie

But I using upflow code, convert fail

Why???

Spent some of minute

There are some wrong character in table "member"

Clean table

Ping pong ping pong