pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrate postgres databases from SQL_ASCII to UNICODE encoding
9+ messages / 6 participants
[nested] [flat]
* Migrate postgres databases from SQL_ASCII to UNICODE encoding
@ 2004-07-17 20:03 Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Dario V. Fassi @ 2004-07-17 20:03 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org
A simple question, we need to migrate many (>20) postgres databases from
SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server.
With Dump/Restore , we get an error (Invalid Unicode) in any field that
has a 8 bits character coming from the SQL_ASCII , even setting the
client_encoding to WIN, ISO-8859-1, and others encodings.
I'm really worried about this problem since , we have many DBs to
migrate and even with a ODBC program we can't port tables from
Postgresql/SQL_ASCII to Postgresql/UNICODE.
Any suggestion will be *very* appreciated.
Regards,
Dario.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-17 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-07-17 21:18 UTC (permalink / raw)
To: Dario V. Fassi <software@sistemat.com.ar>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org
"Dario V. Fassi" <software@sistemat.com.ar> writes:
> A simple question, we need to migrate many (>20) postgres databases from
> SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server.
SQL_ASCII is not an encoding (it's more like the absence of knowledge
about an encoding). What is the data actually stored as?
> With Dump/Restore , we get an error (Invalid Unicode) in any field that
> has a 8 bits character coming from the SQL_ASCII , even setting the
> client_encoding to WIN, ISO-8859-1, and others encodings.
It might work to just UPDATE pg_database to set datencoding to the
correct value reflecting what you have actually stored. You might then
need to REINDEX any indexes on textual columns, but I don't think
anything else would go wrong.
If you have a mishmash of different encodings in a single database, then
of course there is no simple solution; you are in for some pain while
you try to fix the data.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-07-17 23:26 ` Dario V. Fassi <software@sistemat.com.ar>
2004-07-18 02:00 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Andrew Lazarus <drlaz@attglobal.net>
2004-07-18 06:29 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 08:28 ` Re: [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Peter Eisentraut <peter_e@gmx.net>
0 siblings, 3 replies; 9+ messages in thread
From: Dario V. Fassi @ 2004-07-17 23:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Oliver Jowett <oliver@opencloud.com>; pgsql-sql@postgresql.org, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Tom Lane wrote:
>"Dario V. Fassi" <software@sistemat.com.ar> writes:
>
>
>>A simple question, we need to migrate many (>20) postgres databases from
>>SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server.
>>
>>
>
>SQL_ASCII is not an encoding (it's more like the absence of knowledge
>about an encoding). What is the data actually stored as?
>
>
>>With Dump/Restore , we get an error (Invalid Unicode) in any field that
>>has a 8 bits character coming from the SQL_ASCII , even setting the
>>client_encoding to WIN, ISO-8859-1, and others encodings.
>>
>>
>
>It might work to just UPDATE pg_database to set datencoding to the
>correct value reflecting what you have actually stored. You might then
>need to REINDEX any indexes on textual columns, but I don't think
>anything else would go wrong.
>
>If you have a mishmash of different encodings in a single database, then
>of course there is no simple solution; you are in for some pain while
>you try to fix the data.
>
>
Yes you are right , the original data come from a DB2 with CodePage
IBM-850 and was inserted without complains in a Postgres 7.3.6 with
SQL_ASCII.
Now we are in a Jail , because IBM-850 , isn't WIN, isn't ISO-xx ,
isn't no one postgresql's encoding.
So when in change via pg_databases the encoding , 8 bits characters
become garbage.
More even if we accept this garbage chars and we set encoding to e.g.
ISO-8859-1 it's impossible go to a UNICODE because this garbage chars
are invalid in client's encoding , so they are reject (in translation
process as invalid unicode chars).
We are in a big problem, and the only way out I can imagine is fix the
data by hand :-! .
Dario,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-18 02:00 ` Andrew Lazarus <drlaz@attglobal.net>
2 siblings, 0 replies; 9+ messages in thread
From: Andrew Lazarus @ 2004-07-18 02:00 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
If you pg_dump in ASCII format, could you figure out what has happened to
the 8-bit characters, if they are recognizable garbage (not truncated to
some genuine character) you then write a sed script. It by hand, but it
doesn't sound terrible.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-18 06:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 19:47 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-07-18 06:29 UTC (permalink / raw)
To: Dario V. Fassi <software@sistemat.com.ar>; +Cc: Oliver Jowett <oliver@opencloud.com>; pgsql-sql@postgresql.org, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
"Dario V. Fassi" <software@sistemat.com.ar> writes:
>> SQL_ASCII is not an encoding (it's more like the absence of knowledge
>> about an encoding). What is the data actually stored as?
> Yes you are right , the original data come from a DB2 with CodePage
> IBM-850 and was inserted without complains in a Postgres 7.3.6 with
> SQL_ASCII.
Ugh. You'll have to work out how to convert that codepage to one of the
encodings that PG supports. Or else add it as a supported encoding
(I'm not sure how hard that is, but it's not out of the question).
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2004-07-18 06:29 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-07-18 19:47 ` Dario V. Fassi <software@sistemat.com.ar>
2004-07-19 10:25 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Michael Stephenson <mstephenson@openworld.org>
2004-07-19 12:46 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Xavier Poinsard <xpoinsard@openpricer.com>
0 siblings, 2 replies; 9+ messages in thread
From: Dario V. Fassi @ 2004-07-18 19:47 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-jdbc@postgresql.org <pgsql-jdbc@postgresql.org>
Tom Lane wrote:
>"Dario V. Fassi" <software@sistemat.com.ar> writes:
>
>
>>>SQL_ASCII is not an encoding (it's more like the absence of knowledge
>>>about an encoding). What is the data actually stored as?
>>>
>>>
>>Yes you are right , the original data come from a DB2 with CodePage
>>IBM-850 and was inserted without complains in a Postgres 7.3.6 with
>>SQL_ASCII.
>>
>>
>
>Ugh. You'll have to work out how to convert that codepage to one of the
>encodings that PG supports. Or else add it as a supported encoding
>(I'm not sure how hard that is, but it's not out of the question).
>
> regards, tom lane
>
>
Ok, I'm interested is do that , but someone is the development group
would give me some assistence to get a start point to work with.
I program in Java, C, and less frequently in C++.
Dario.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2004-07-18 06:29 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 19:47 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-19 10:25 ` Michael Stephenson <mstephenson@openworld.org>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Stephenson @ 2004-07-19 10:25 UTC (permalink / raw)
To: Dario V. Fassi <software@sistemat.com.ar>; +Cc: pgsql-sql@postgresql.org, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
What we've done in the past is just run the dump file through iconv - this has
support for IBM-850.
http://www.gnu.org/software/libiconv/documentation/libiconv/iconv.1.html
Hope this helps,
Michael
Dario V. Fassi wrote:
>
> Tom Lane wrote:
>
>>"Dario V. Fassi" <software@sistemat.com.ar> writes:
>>
>>
>>>>SQL_ASCII is not an encoding (it's more like the absence of knowledge
>>>>about an encoding). What is the data actually stored as?
>>>>
>>>>
>>>Yes you are right , the original data come from a DB2 with CodePage
>>>IBM-850 and was inserted without complains in a Postgres 7.3.6 with
>>>SQL_ASCII.
>>>
>>>
>>
>>Ugh. You'll have to work out how to convert that codepage to one of the
>>encodings that PG supports. Or else add it as a supported encoding
>>(I'm not sure how hard that is, but it's not out of the question).
>>
>> regards, tom lane
>>
>>
> Ok, I'm interested is do that , but someone is the development group
> would give me some assistence to get a start point to work with.
> I program in Java, C, and less frequently in C++.
>
> Dario.
>
>
--
Web Applications Developer
Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ.
Tel: +44 1225 444950 Fax: +44 1225 336738 http://www.openworld.co.uk/
CONFIDENTIALITY NOTICE
The information contained in this message is confidential, intended only for
the use of the individual or the entity named as recipient. If the reader of
this message is not that recipient, you are notified that any dissemination,
distribution or copy of this message is strictly prohibited. If you have
received this message in error, please immediately notify us by telephone on
the number above. Your co-operation is appreciated.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2004-07-18 06:29 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 19:47 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-19 12:46 ` Xavier Poinsard <xpoinsard@openpricer.com>
1 sibling, 0 replies; 9+ messages in thread
From: Xavier Poinsard @ 2004-07-19 12:46 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
Dario V. Fassi a écrit :
>
> Tom Lane wrote:
>
>>"Dario V. Fassi" <software@sistemat.com.ar> writes:
>>
>>
>>>>SQL_ASCII is not an encoding (it's more like the absence of knowledge
>>>>about an encoding). What is the data actually stored as?
>>>>
>>>>
>>>Yes you are right , the original data come from a DB2 with CodePage
>>>IBM-850 and was inserted without complains in a Postgres 7.3.6 with
>>>SQL_ASCII.
>>>
>>>
>>
>>Ugh. You'll have to work out how to convert that codepage to one of the
>>encodings that PG supports. Or else add it as a supported encoding
>>(I'm not sure how hard that is, but it's not out of the question).
>>
did you try the recode utility ?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
@ 2004-07-18 08:28 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 9+ messages in thread
From: Peter Eisentraut @ 2004-07-18 08:28 UTC (permalink / raw)
To: Dario V. Fassi <software@sistemat.com.ar>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Dario V. Fassi wrote:
> Yes you are right , the original data come from a DB2 with CodePage
> IBM-850 and was inserted without complains in a Postgres 7.3.6 with
> SQL_ASCII.
If you have a program named "recode" installed you could try using that
to recode the dump file to a supported encoding.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-07-19 12:46 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-17 20:03 Migrate postgres databases from SQL_ASCII to UNICODE encoding Dario V. Fassi <software@sistemat.com.ar>
2004-07-17 21:18 ` Re: Migrate postgres databases from SQL_ASCII to UNICODE encoding Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 23:26 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2004-07-18 02:00 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Andrew Lazarus <drlaz@attglobal.net>
2004-07-18 06:29 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 19:47 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Dario V. Fassi <software@sistemat.com.ar>
2004-07-19 10:25 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Michael Stephenson <mstephenson@openworld.org>
2004-07-19 12:46 ` Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Xavier Poinsard <xpoinsard@openpricer.com>
2004-07-18 08:28 ` Re: [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox