Re: [GENERAL] Encoding problem using pg_dumpall
Tom Lane wrote: > Magnus Hagander writes: >> Tom Lane wrote: >>> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, >>> which seems like an oversight. So you need to fix your locale, >>> or else use pg_dump directly.) > >> IIRC, you can't set the windows console to be UTF8. > > Ugh. That seems to raise the priority of having a -E switch quite > a lot. I'm surprised no one has complained of this before. Most people use pg_dump. I know I recommend everbody to use pg_dump to dump the database, because you can use -Fc. Then just use pg_dumpall to dump the globals, and they normally don't have any non-ascii in them. > I think it should be possible to work around it by setting > PGCLIENTENCODING in the environment, but I dunno how to do that > on Windows. That's a simple set PGCLIENTENCODING=UTF8 //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Encoding problem using pg_dumpall
Magnus Hagander writes: > Tom Lane wrote: >> (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, >> which seems like an oversight. So you need to fix your locale, >> or else use pg_dump directly.) > IIRC, you can't set the windows console to be UTF8. Ugh. That seems to raise the priority of having a -E switch quite a lot. I'm surprised no one has complained of this before. I think it should be possible to work around it by setting PGCLIENTENCODING in the environment, but I dunno how to do that on Windows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Encoding problem using pg_dumpall
Tom Lane wrote: > "Moshe Ben-Shoham" writes: >> C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint > >> c:\temp\dbdump.sql >> pg_dump: SQL command failed >> pg_dump: Error message from server: ERROR: character 0xd595 of encoding >> "UTF8" has no equivalent in "WIN1252" > > Apparently you have WIN1252 set as the default client encoding, probably > via an environment variable or locale setting. Either get rid of it, > or override it by including "-E UTF8" in the pg_dump command. > > (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, > which seems like an oversight. So you need to fix your locale, > or else use pg_dump directly.) IIRC, you can't set the windows console to be UTF8. Thus, your option is to switch to using pg_dump and use -E UTF8 on that one. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Encoding problem using pg_dumpall
"Moshe Ben-Shoham" writes: > C:\Program Files\PostgreSQL\8.3\bin>pg_dumpall -U admint > > c:\temp\dbdump.sql > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: character 0xd595 of encoding > "UTF8" has no equivalent in "WIN1252" Apparently you have WIN1252 set as the default client encoding, probably via an environment variable or locale setting. Either get rid of it, or override it by including "-E UTF8" in the pg_dump command. (Hmm, actually it looks like pg_dumpall hasn't got a -E switch, which seems like an oversight. So you need to fix your locale, or else use pg_dump directly.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] encoding problem at restore
On Sat, Feb 17, 2007 at 03:12:44AM -0800, Bob Hunter wrote: > ERROR: invalid byte sequence for encoding "UTF8": > 0xe02031 > HINT: This error can also happen if the byte sequence > does not match the encoding expected by the server, > which is controlled by "client_encoding". > CONTEXT: COPY , line 1270 > > There are two problems. The first is, why UTF8 at all, > given that the dump specifies SQL_ASCII? Probably because the database encoding is UTF-8. You can check with "SHOW server_encoding", or with \l in psql, or by running "psql -l" from a shell prompt, etc. With a client_encoding of SQL_ASCII no conversion will be made, so if the data isn't already UTF-8 then you get an error such as the above. > The second is, that at line 1270 there are (unsurprisingly) only > ASCII characters, so why is psql complaining at all? Are you sure you're looking at the right line? The line number in the error refers to the line of the COPY data, not to the line of the input file or stream. For example, if the COPY begins on line 67 of the dump file then line 1270 of the data would be line 1337 of the file. If you look at the correct line you might find a string like "à 1" (latin small letter a with grave, space, digit one). Try editing the client_encoding line to specify whatever encoding the data is really in. For Western European languages likely guesses are LATIN1, LATIN9, or WIN1252 (especially the latter if the data originated on Windows). Alternatively, you could use a converter like iconv or uconv to convert the file to UTF-8 before feeding it to psql. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Encoding problem
Albe Laurenz a écrit :
I am trying to remove accents from a string. I found a nice
solution for this on postgresqlfr, using the to_ascii()
function.
Now, the problem I have is :
mydb=# SELECT to_ascii(convert('abcdef', 'LATIN9'));
ERROR: encoding conversion from UTF8 to ASCII not supported
Why is the conversion to LATIN9 not working as expected ?
My database's encoding is UTF8.
Maybe you actually want to
test=> select to_ascii(convert('ábcdêf', 'LATIN9'), 'LATIN9');
to_ascii
--
abcdef
(1 row)
Indeed !!!
Thanks a lot !
I suppose that not giving the encoding to 'to_ascii'
defaulted to the database encoding ?
Sorry for this mistake, I did not realize that this setting
existed for the to_ascii function...
Thanks again !
--
Arnaud
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Encoding problem
> I am trying to remove accents from a string. I found a nice
> solution for this on postgresqlfr, using the to_ascii()
> function.
>
> Now, the problem I have is :
>
> mydb=# SELECT to_ascii(convert('abcdef', 'LATIN9'));
> ERROR: encoding conversion from UTF8 to ASCII not supported
>
> Why is the conversion to LATIN9 not working as expected ?
> My database's encoding is UTF8.
Maybe you actually want to
test=> select to_ascii(convert('ábcdêf', 'LATIN9'), 'LATIN9');
to_ascii
--
abcdef
(1 row)
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] encoding problem
jef peeraer schreef: i never thought i would be bblocked by an encoding problem :-( My database is in LATIN1 , i have entries like this in a table called gemeenten Column | Type | Modifiers ---+--+ id| integer | serial gemeente | text | not null postcode | smallint | not null provincies_id | integer | This data is copied from a dump from that table 9780Quévy70407 9781Quévy-le-Grand70407 9782Quévy-le-Petit70407 So, the accents are there. But with my web page, which is set to ISO-8859-1, i don't get the accents. The web-pages are build with XUL, where i set the charset to ISO-8859-1, but communication with the server is through XMLHttpRequest. Do I have to specify the charset as well in the communication between server and client ? Or where else could it go wrong. jef peeraer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster thanks for the help. The problem was lying in the fact that use json_encode procedure from PHP. This procedure requires that the string to be encoded be in UTF-8 format. So basically, my database stays in LATIN1 and i set client encoding to UTF-8, as well as all web pages. jef peeraer ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] encoding problem
Hi Jef,I use the prototype 1.4 to ajax some web pages. I have to encodeURI the post form data especial the string form value, otherwise the server will receive wrong encoding characters.If you can not see the query result in correct web page encoding, maybe the page container of this XUL ajax control is not match of your setting of the web page.regards Steve Yao-原始邮件-发件人:"jef peeraer" <[EMAIL PROTECTED]>发送时间:2006-10-13 17:14:53收件人:pgsql-general@postgresql.org抄送:(无)主题:[GENERAL] encoding problemi never thought i would be bblocked by an encoding problem :-(My database is in LATIN1 , i have entries like this in a table called gemeenten Column | Type | Modifiers---+--+ id| integer | serial gemeente | text | not null postcode | smallint | not null provincies_id | integer |This data is copied from a dump from that table9780 Quévy 7040 79781 Quévy-le-Grand 7040 79782 Quévy-le-Petit 7040 7So, the accents are there. But with my web page, which is set to ISO-8859-1, i don't get the accents.The web-pages are build with XUL, where i set the charset to ISO-8859-1,but communication with the server is through XMLHttpRequest.Do I have to specify the charset as well in the communication between server and client ? Or where else could it go wrong.jef peeraer---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster 这 些 让 她 幸 福 迭 起 ( 图 ) 汗 ! 女 人 穿 这 些 最 迷 人 , 女 友 亲 自 给 演 示 ( 组 图 )
Re: [GENERAL] encoding problem
jef peeraer wrote: i never thought i would be bblocked by an encoding problem :-( My database is in LATIN1 , i have entries like this in a table called gemeenten Column | Type | Modifiers ---+--+ id| integer | serial gemeente | text | not null postcode | smallint | not null provincies_id | integer | This data is copied from a dump from that table 9780Quévy70407 9781Quévy-le-Grand70407 9782Quévy-le-Petit70407 So, the accents are there. But with my web page, which is set to ISO-8859-1, i don't get the accents. The web-pages are build with XUL, where i set the charset to ISO-8859-1, but communication with the server is through XMLHttpRequest. Do I have to specify the charset as well in the communication between server and client ? Or where else could it go wrong. jef peeraer I am not sure where your problem is, but we have used a PostgreSQL database with the default encoding (ISO something or ANSI something, can't recall right now, but not Unicode or so) for several years storing all kind of encodings inside and outputting them successfully. Only the browser encoding must match the original encoding. Don't know if this helps, just wanted to give you our example. All best, Iv ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Encoding problem
On Aug 30, 2006, at 6:29 AM, Pantelis Natsiavas wrote: In addition trying to make the same query through the query tool of pgadmin, I receive the same answer and of course null results. I don't know if it matters, but I am using Windows XP and PostgreSQL 8.1 with JDBC 3. I have checked everything that has to do with the java part of encoding and I cannot find anything wrong. The fact that I could not make the query through the pgadmin query tool, shows that the problem lays at the PostgreSQL part. What could I do? Can anyone help me? A few issues to check: 1. pgadmin will double quote table names that have special characters, mixed case, or reserved words. So double check the exact table name and perhaps try double quoting it in your query. 2. Be sure your table's schema is on the search_path. 3. Double check the value of client_encoding. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] encoding problem
On 12/1/05, marcelo Cortez <[EMAIL PROTECTED]> wrote: > i have problems with encodings PostgreSQL case conversion functions is a little bit buggy. (Especially for Latin-N and Unicode encodings.) I've prepared a patch [1] to fix a similar problem for Latin5 encoding. It wasn't tested so much but works for your problem too: template1=# CREATE DATABASE "testLatin" ENCODING = 'LATIN9'; CREATE DATABASE template1=# \c testLatin You are now connected to database "testLatin". testLatin=# CREATE TABLE test testLatin-# ( nombre varchar(20)); CREATE TABLE testLatin=# COPY test FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> maricón >> ñañoso pícaro >> \. testLatin=# select * from test where upper(nombre) like 'ÑA%'; nombre --- ñañoso pícaro (1 row) [1] You can find related patch (and discussion) @ http://archives.postgresql.org/pgsql-patches/2005-11/msg00173.php address. It fixes case conversion problems for ILIKE, upper() and lower() functions. Regards. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] encoding problem
--- Tom Lane <[EMAIL PROTECTED]> escribió: > marcelo Cortez <[EMAIL PROTECTED]> writes: > > i have problems with encodings > > You need to make sure that the database locale > matches what you want, > not only the encoding. > > See the "Localization" chapter in the docs: > http://www.postgresql.org/docs/8.1/static/charset.html > > regards, tom lane > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] encoding problem
marcelo Cortez <[EMAIL PROTECTED]> writes: > i have problems with encodings You need to make sure that the database locale matches what you want, not only the encoding. See the "Localization" chapter in the docs: http://www.postgresql.org/docs/8.1/static/charset.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Encoding problem
[EMAIL PROTECTED] (Andreas Seltenreich) wrote in message news:<[EMAIL PROTECTED]>... > Fritz Bayer schrob: > > > The problem is that alls the "ü" characters get displayed as "". > > > > Why is that so? > > This could happen when your locale isn't properly set up. If, for > example, LC_CTYPE is set to C, your pager thinks this character isn't > printable and tries to do something smart with it. You can display the > current setup by running "locale". > > Does it work when you export LC_CTYPE=de_DE before running psql? > I noticed that no locales have been generated and that the variable was set to C. I created the locales for LATIN1 and ISO8859-15 and set the variable to the value you suggested. I tried it and now I get ü instead. To give you more clues I checked the encoding which seems to be UNICODE. show client_encoding; NOTICE: Current client encoding is 'UNICODE' SHOW VARIABLE So I tried to set it to LATIN1 using \encoding latin1 But now I get select * from user_requests; ERROR: Could not convert UTF-8 to ISO8859-1 > regards, > Andreas > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Encoding problem
Fritz Bayer schrob: > The problem is that alls the "ü" characters get displayed as "". > > Why is that so? This could happen when your locale isn't properly set up. If, for example, LC_CTYPE is set to C, your pager thinks this character isn't printable and tries to do something smart with it. You can display the current setup by running "locale". Does it work when you export LC_CTYPE=de_DE before running psql? regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Encoding problem
Fritz Bayer wrote: Hi, when I enter the following SELECT into psql select * from mytable where data like '%ü%' I get a selection of a couple of rows. The problem is that alls the "ü" characters get displayed as "". Why is that so? Probably something in your terminal setup. I always have endless trouble with accented characters on the different systems I use. fritz BTW: I set client encoding to latin1 and the database stores data in utf8 and I'm running postgresql under the following system: PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 You should upgrade to the most recent 7.2.x version immediately, and I'd recommend 8.0.x as soon as possible. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
