Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
At Thu, 6 Jan 2005 16:16:05 +1100 (EST), Stuart Guthrie wrote: So Debian seems to be initialising postgres dbs with the first of these collate sequences meaning that whatever 'C' is seems to return case-sensitive search results. Mandrake's standard RPM implements postgres with en_US collation sequences which means it sorts AaBbCcDd which is what I'd be hoping for. Run dpkg-reconfigure --priority=low postgresql. One of the questions is what encoding you want to use by default (although since you obviously *rely* on the encoding, then I'd suggest mentioning it explicitly in the CREATE DATABASE statement anyway). -- - Gus -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
Thanks Gus I solved it with a dump/initdb --locale=/load. Possibly not the deb-done-thing? I will get our friendly local deb-head to fix it all nicely on return from Hols. Works a treat. Stu At Thu, 6 Jan 2005 16:16:05 +1100 (EST), Stuart Guthrie wrote: So Debian seems to be initialising postgres dbs with the first of these collate sequences meaning that whatever 'C' is seems to return case-sensitive search results. Mandrake's standard RPM implements postgres with en_US collation sequences which means it sorts AaBbCcDd which is what I'd be hoping for. Run dpkg-reconfigure --priority=low postgresql. One of the questions is what encoding you want to use by default (although since you obviously *rely* on the encoding, then I'd suggest mentioning it explicitly in the CREATE DATABASE statement anyway). -- - Gus -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
[SLUG] Postgresql case-insensitive mishmash brain twister.
Here is the SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.status as status, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (claimspaym0_.status='en' ) order by claimspaym0_.status limit ? For one DB on a debian server with postgres 7.4.6 this returns no rows. For another DB on a mandrake laptop with postgres 7.4.1 this returns the correct 10 rows Is it something I said or is there a CREATEDB switch or something? Anyone know? HNY one and all. Stuart -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
My guess is the limit clause is doing something odd. Try limiting using LIMIT 10 OFFSET 0 if you need a limit (that was a guess from the LIMIT man page - I'm not that familiar with progresql) HTH Rob. On Thu, 6 Jan 2005 12:22:20 +1100 (EST), [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Here is the SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.status as status, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (claimspaym0_.status='en' ) order by claimspaym0_.status limit ? For one DB on a debian server with postgres 7.4.6 this returns no rows. For another DB on a mandrake laptop with postgres 7.4.1 this returns the correct 10 rows Is it something I said or is there a CREATEDB switch or something? Anyone know? HNY one and all. Stuart -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- Rob Sharp e: [EMAIL PROTECTED] w: quannum.co.uk j: [EMAIL PROTECTED] -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
Nope but a nice try... Same SQL query both servers. Different result. Case insensitive searching is what I'm after as a default. Stu My guess is the limit clause is doing something odd. Try limiting using LIMIT 10 OFFSET 0 if you need a limit (that was a guess from the LIMIT man page - I'm not that familiar with progresql) HTH Rob. On Thu, 6 Jan 2005 12:22:20 +1100 (EST), [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Here is the SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.status as status, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (claimspaym0_.status='en' ) order by claimspaym0_.status limit ? For one DB on a debian server with postgres 7.4.6 this returns no rows. For another DB on a mandrake laptop with postgres 7.4.1 this returns the correct 10 rows Is it something I said or is there a CREATEDB switch or something? Anyone know? HNY one and all. Stuart -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- Rob Sharp e: [EMAIL PROTECTED] w: quannum.co.uk j: [EMAIL PROTECTED] -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
Here is the SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.status as status, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (claimspaym0_.status='en' ) order by claimspaym0_.status limit ? 1) claimspaym0_ is not a very nice name for a database table in my opinion, it was better as claims_payments before you aliased it. dare i suggest something like select id, member_id, policy_id, entry_date, family_name, status, amount, coverage from claims_payments where (status = 'en') order by status limit 10; since you're only selecting against the one table, the aliasing is unneeded and confusing I suspect. 2) run select count(*) from claims_payments; on both the debian system and the mandrake system and check that the numbers are the same. If they're not, your clients are pointing at different databases and you're getting results on different data. perhaps one of your systems is pointing at a development database and the other at a production database, or the databases are out of sync, or somehow databases which you think are identical aren't. Is it something I said or is there a CREATEDB switch or something? Anyone know? It won't have anything to do with CREATEDB. Good luck, Stuart. Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
On Thu, 2005-01-06 at 13:08 +1100, [EMAIL PROTECTED] wrote: Nope but a nice try... Same SQL query both servers. Different result. Case insensitive searching is what I'm after as a default. I'm sorry, that query is far too white-space challenged for me to be able to parse it, so I can't be much help in correcting your query. I will say that if you want to do a case insensitive string match, 'ilike' is the operator you're looking for. If you're seeing different string comparison behaviour between machines, I'd be checking the encoding type of the data. Unfortunately I can't remember how to do that, so you'll need to look it up. It's done on a database level, IIRC. HTH, James. signature.asc Description: This is a digitally signed message part -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
The SQL is not the problem. It's generated by Hibernate (www.hibernate.org) and I've checked the generated SQL on both machines... It's the results that worry me.. Your other point about the actual data is a good one but also covered. The data is the same for the query set that I request. Maybe this is clearer select * from claims_payments where (status='en' ) order by status For the 7.4.1 psql DB produces results that include status' of 'ENTERED'. For the 7.4.6 psql DB produces no results as the query seems to handle the request as case-sensitive for this DB but not the other one. select * from claims_payments where (status='EN' ) order by status produces the same data on from both DBs. I can do the same thing on all VARCHAR fields in the DB. On the 7.4.1 DB, I get case insensitive results, on 7.4.6 DB I get case-sensitive results. I'm thinking it's not version related, its the Mandrake-build vs the Debian Build or some other parameter in Postgres itself??? Thanks for your suggestion Stuart Here is the SQL: select claimspaym0_.id as id, claimspaym0_.member_id as member_id, claimspaym0_.policy_id as policy_id, claimspaym0_.entry_date as entry_date, claimspaym0_.family_name as family_n5_, claimspaym0_.status as status, claimspaym0_.amount as amount, claimspaym0_.coverage as coverage from claims_payments claimspaym0_ where (claimspaym0_.status='en' ) order by claimspaym0_.status limit ? 1) claimspaym0_ is not a very nice name for a database table in my opinion, it was better as claims_payments before you aliased it. dare i suggest something like select id, member_id, policy_id, entry_date, family_name, status, amount, coverage from claims_payments where (status = 'en') order by status limit 10; since you're only selecting against the one table, the aliasing is unneeded and confusing I suspect. 2) run select count(*) from claims_payments; on both the debian system and the mandrake system and check that the numbers are the same. If they're not, your clients are pointing at different databases and you're getting results on different data. perhaps one of your systems is pointing at a development database and the other at a production database, or the databases are out of sync, or somehow databases which you think are identical aren't. Is it something I said or is there a CREATEDB switch or something? Anyone know? It won't have anything to do with CREATEDB. Good luck, Stuart. Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
Re: [SLUG] Postgresql case-insensitive mishmash brain twister.
And the winner is James Gregory. I owe you a beer. Next slug meeting. I'm the one with the 'I Love Mandrake' badge at the back of the room.. lc_collate | C lc_ctype | C lc_messages| unset lc_monetary| C lc_numeric | C lc_time| C log_connections| on --- krb_server_keyfile | unset lc_collate | en_US lc_ctype | en_US lc_messages| en_US lc_monetary| en_AU So Debian seems to be initialising postgres dbs with the first of these collate sequences meaning that whatever 'C' is seems to return case-sensitive search results. Mandrake's standard RPM implements postgres with en_US collation sequences which means it sorts AaBbCcDd which is what I'd be hoping for. There is an initdb option to change this standard behaviour. http://www.postgresql.org/docs/7.3/static/charset.html Was good for me. Other options to do with other 'where' parameters might have worked but would probably have lead to slower performance (lots of serial reads, no chance to use indexes) and icky workarounds. HTH Someone else someday. I did do a lot of googling for this subject but nothing jumped out at me. Thanks James and everyone else who helped.. Stuart On Thu, 2005-01-06 at 13:08 +1100, [EMAIL PROTECTED] wrote: Nope but a nice try... Same SQL query both servers. Different result. Case insensitive searching is what I'm after as a default. I'm sorry, that query is far too white-space challenged for me to be able to parse it, so I can't be much help in correcting your query. I will say that if you want to do a case insensitive string match, 'ilike' is the operator you're looking for. If you're seeing different string comparison behaviour between machines, I'd be checking the encoding type of the data. Unfortunately I can't remember how to do that, so you'll need to look it up. It's done on a database level, IIRC. HTH, James. -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
