pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Question about encoding 3+ messages / 2 participants [nested] [flat]
* Question about encoding @ 2009-08-24 16:42 Denis BUCHER <dbucherml@hsolutions.ch> 2009-08-24 22:42 ` Re: Question about encoding Peter Eisentraut <peter_e@gmx.net> 0 siblings, 1 reply; 3+ messages in thread From: Denis BUCHER @ 2009-08-24 16:42 UTC (permalink / raw) To: pgsql-sql Hello, I'm sure you already replied tons of questions like this, but I don't understand something. This is the situation : * I have COPY commands from a dump, encoded as UTF8 (special characters are encoded as 2-bytes). * My file contains SET client_encoding = 'UTF8'; * But when I do psql <myfile.sql I get an error for 40 characters long strings that have UTF8 characters : ERROR: value too long for type character varying(40) * I think I understand why, psql -l says that my database is SQL_ASCII * Postgresql 7.4 Question 1 : Is it the expected behavior ? These characters have a SQL_ASCII equivalent because I already have them stored in another table of the same database Question 2 : If yes, then I suppose I should have the database as LATIN1 or UTF8. Can I change/convert the encoding of the database ? Or at least of the schema (which would be even better) Thanks a lot for any help :-))) Denis ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about encoding 2009-08-24 16:42 Question about encoding Denis BUCHER <dbucherml@hsolutions.ch> @ 2009-08-24 22:42 ` Peter Eisentraut <peter_e@gmx.net> 2009-08-25 20:16 ` Re: Question about encoding Denis BUCHER <dbucherml@hsolutions.ch> 0 siblings, 1 reply; 3+ messages in thread From: Peter Eisentraut @ 2009-08-24 22:42 UTC (permalink / raw) To: Denis BUCHER <dbucherml@hsolutions.ch>; +Cc: pgsql-sql On mån, 2009-08-24 at 18:42 +0200, Denis BUCHER wrote: > Question 1 : > Is it the expected behavior ? These characters have a SQL_ASCII > equivalent because I already have them stored in another table of the > same database SQL_ASCII is not the same as ASCII. SQL_ASCII means, take the bytes as they come. So a 40-character UTF-8 string might indeed be longer than 40 bytes, which is what SQL_ASCII will look at. The best bet is to avoid SQL_ASCII altogether. It's pretty bogus and inconsistent. > Question 2 : > If yes, then I suppose I should have the database as LATIN1 or UTF8. > Can I change/convert the encoding of the database ? Or at least of the > schema (which would be even better) Dump, recreate database with right encoding, restore. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about encoding 2009-08-24 16:42 Question about encoding Denis BUCHER <dbucherml@hsolutions.ch> 2009-08-24 22:42 ` Re: Question about encoding Peter Eisentraut <peter_e@gmx.net> @ 2009-08-25 20:16 ` Denis BUCHER <dbucherml@hsolutions.ch> 0 siblings, 0 replies; 3+ messages in thread From: Denis BUCHER @ 2009-08-25 20:16 UTC (permalink / raw) To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql Hello, Peter Eisentraut a écrit : >> Question 1 : >> Is it the expected behavior ? These characters have a SQL_ASCII >> equivalent because I already have them stored in another table of the >> same database > > SQL_ASCII is not the same as ASCII. SQL_ASCII means, take the bytes as > they come. So a 40-character UTF-8 string might indeed be longer than > 40 bytes, which is what SQL_ASCII will look at. > > The best bet is to avoid SQL_ASCII altogether. It's pretty bogus and > inconsistent. OK that's a good point it explains everything. >> Question 2 : >> If yes, then I suppose I should have the database as LATIN1 or UTF8. >> Can I change/convert the encoding of the database ? Or at least of the >> schema (which would be even better) > > Dump, recreate database with right encoding, restore. Ok I did it but postgresql 7.4 was not very nice, the following failed : 1. dump 2. DROP DATABASE; CREATE DATABASE WITH ENCODING UTF8 3. restore And it bugged. In fact the dump was writtent with "SET CLIENT ENCODING=SQL_ASCII but when restoring on the UTF8 databse it failed, I had to change it to SET client_encoding = LATIN1. Anyway, finally everything is working again, I just had to add a SET NAMES LATIN1 to my application because it was ISO and not UTF8 aware. Therefore thanks a lot for your help, it solved all my problems ! Denis ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-08-25 20:16 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-08-24 16:42 Question about encoding Denis BUCHER <dbucherml@hsolutions.ch> 2009-08-24 22:42 ` Peter Eisentraut <peter_e@gmx.net> 2009-08-25 20:16 ` Denis BUCHER <dbucherml@hsolutions.ch>
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