[GENERAL] psql file restore - problem with encoding
Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] psql file restore - problem with encoding
Just update - the system is Fedora Core 2, but the PostgreSQL is compiled from sources. Miroslav ulc wrote: Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(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
Re: [GENERAL] psql file restore - problem with encoding
John DeSoi wrote: On Mar 16, 2005, at 9:34 AM, Miroslav ulc wrote: I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: Maybe try setting the client side encoding explicitly at the top of the dump file: \encoding UNICODE It should also be possible to set this in the psql command line if you don't want to modify the file. The dump file already contains the encoding information as the first command: SET client_encoding = 'UNICODE'; so I think this is sufficient. What is strange to me is that the communication is UNICODE - UNICODE so for me it means no conversion, but the result is different. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] psql file restore - problem with encoding (solved)
We have solved the problem. Maybe someone will face the same problem so just to close this thread, the problem was in php.ini where a default output character set was set which caused the second recoding problem (just for display using php). Miroslav ulc wrote: Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Localization problems between Access and PostgresSQL
Secrtariat wrote: Hello, I've dumped the content of MS-Access 2002 SP3 tables on a PC with Windows XP Pro in French localization. Then I COPY these files, on the same PC hosting an PostgreSQL 8.0.1 database. I've problems with the accents !? Why ? Luc, what encoding did you set for your PostgreSQL database? Did you set client encoding in the dump file to the Frech encoding that your Windows use? What kind of encoding must I use to create the PG database under Win XP pro French ? I think you shouldn't use Unicode if you need correct collating order. In this case you should use French encoding aswell (my opinion). Thanks for all your comments Luc Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(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
Re: [GENERAL] Internal account lookup failure
sferriol wrote: hello on windows 2000 (chinese) during postgres8.0.0-rc1.msi installation, it fails in 'Service configuration': Internal account lookup failure Is there a reason for not using version 8.0.1? Maybe this one will install without problem. sylvain Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Hi Rick, the work you are doing is important (at least I think so). From my experience PosgreSQL performance is also very slow in case there are several LEFT JOINs and there are varchar() fields. You can see an example in archive where my problem is described (Subject: How to read query plan). There is a patch that partially solves this problem (as Tom Lane mentioned) but it is not in the latest PostgreSQL release. I will work on improving of the design of tables that I have problem with to see if there will be any significant speed improvements. Maybe you could use some of my results for some of your tests then. Miroslav Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. ... begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL]
Mohsen Pahlevanzadeh wrote:
Dears,
When i wanna insert to my following code,I receive following error:
Insert into c(t) values('ccc'); -- It's my qurey.
Insert command failed!So i can't continue to insert in your table!
ERROR: pg_atoi: error in ccc: can't parse ccc
Usually atoi functions perform conversion from string to integer (if my
memory serves well). Maybe the problem is that you are trying to insert
string into numeric field.
...
Miroslav
begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+420 257 225 602
tel;cell:+420 603 711 413
x-mozilla-html:TRUE
url:http://www.startnet.cz
version:2.1
end:vcard
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] free space map settings
go wrote: Hi Tell me please what does it mean the next hint: TIP 7: don't forget to increase your free space map settings where can i read about this settings? http://www.postgresql.org/docs/8.0/interactive/runtime-config.html -- Igor Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problem with special character on
David Gagnon wrote: Thanks for your answer. The ISP created the db fom me .. So I don't have this information. I search the web to know how to get this info via PgadminIII and I haven't found :-( Is there a way to get this information once the database have been created ? I looked via psql .. I haven`t found either David, you can try 'SHOW ALL' command. Thanks /David Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Problem with special character on
David Gagnon wrote: Hi Thanks for the tips. Locale are the same ... unless I don`t look at the right thing... ... Can you try to run the procedure from some other environment? It seems you use some Java class, so I mean try psql, phpPgAdmin or something different. Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] General query optimization howto
Bruce Momjian wrote: Have you read the FAQ? Yes, but I have found only some useful information saying when indexes are not used. Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] General query optimization howto
Peter Eisentraut wrote: Miroslav ¦ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. I doubt that there is a generic documentation on SQL optimization, because this heavily depends on the particular implementation that you are using. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. You should probably start with the Performance Tips chapter in the PostgreSQL documentation. I've read that one. Sure it is good starting point but for a newbie (= me) it's not enough :-( Miroslav ¦ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] General query optimization howto
Hi all, is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. -- Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Locale problem
go wrote: Hi ! Im running PG 7.23 now with text fields in CP866. I tried to restore dump from 7.23 into PG 8.01 with following settings : - initdb --locale ru_RU - createdb -E ALT. all works great , but sort order in text fields is wrong Both on Linux and Windows you should use for your databases the same encoding as your system supports. On Linux you can see your locales using 'locale -a'. On my Linux I have these ru_RU.*: ru_RU ru_RU.iso88595 ru_RU.koi8r ru_RU.utf8 I would try 'initdb --locale ru_RU.utf8' (or any other with specified encoding) and set the same encoding for the database (= in this example 'UNICODE'). Then the sorting should be correct. I'm just a newbie for PostgreSQL so somebody might explain it more precisely. People, help pls to megrate to PG 8.01 properly Thanks, and have a nice day!! Igor Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgres 7.4 build for win
Hi Amin, maybe I'm little off but why don't you try the 8.0.1 version which should run on Windows platform? Miroslav ulc Amin Abdulghani wrote: Hi, I am trying to build postgres 7.4.5 on windows (200) on mingw/msys env. I have installed win32-pthreads to enable-thread safet feature. Couple of issues that I wanted to check:. i) The configure was --enablethread-safety flag for the platform (it was complaining CANNOT enable threads on your platform). I copied src/template/linux to src/template/win32 (most of the contents seemed thread related) and the error was gone. Does this fix seem reasonable. ii) I am now getting an error that postgres doesnt have native spinlock on this platform and to disable the spinlock. Is this the expected behavior for the platform. Thanks.. Amin ---(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 begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PRoblems instalation PostgreeSQL8.0.1 in WindowsXP
trilcejf wrote: Hi there. When i try pass the screen Initialise database cluster appear this message: Tee PostgreeSQL data directory must be on an NTFS formatted volume Any help on this would be appreciated. Thanks in advance Console program 'convert' can convert your FAT to NTFS for you. Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(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
