ColdFusion, Postgres and case sensitivity
Posted by: Dave LOne of the things I run into quite often is case sensitivty in postgres. This ususally happens with CF developers that have only worked in a Microsoft world (IIS, MSSQL, Access). Case problems dealing with the file system are pretty straight forward, if you just stick with all lowercase you are set. However case issues in the postgres database aren't quite that easy. There are two major things you have to deal with:
1.) table and field names
2.) data stored in fields
Now number one is just like the file system if you stick with lowercase table and field names you will have no problems. One of the things I discovered early on is that if your table name is similar to this: MyNewTable. The CF/jdbc driver will pass it to the postgres server as this: mynewtable. There is a way around that and that is to use quotes around the table and fieldnames. So: "MyNewTable" would work just fine, but for portabilities sake just use all lowercase and move on.
Number two is a pretty easy fix as well. I run into this wehn I have to do a text search or some sort of text type matching. There is a function in postgres that will allow you to select a field as all lowercase. It is called LOWER(). Then you just take whatever string has been passed to you and use the CF lcase() function to do the same. Here is a quick example:
SELECT * FROM mynewtable WHERE LOWER(textfield) = #lcase(form.searchword)#
You can also use the LOWER() postgres function as part of the select statement. I have found that this is necessary when building a verity index from query data. Otherwise you will never be able to solve case issues when doing a search. Also you have to use the "as" part or else it will select all fieldnames as the name "lower" for some reason. Here is a quick example of a query to use when indexing table data :
SELECT course_id, LOWER(courses.course_name) as course_name FROM courses
- Permalink - 10:05 AM
- Coldfusion on Linux
- Comments (36)
- Send to Friend
- [+] Add To Hotlist
- Add to del.icio.us
-
Tags: postgres
Track with co.mments
Search
About The Author
Links
Recent Comments
-
mezarlık:
<
http://www.bluehayat.com http://www.bluehayat...
[View] -
wow gold:
<
We supply WoW Gold for wow players, you can B...
[View] -
sado san:
<
wery thanks good sharedsimple machines forum ...
[View] -
sado san:
<
wery thankssimple machines forum smf.gen.tr ö...
[View] -
yat temizliği:
<
thank you wery much
[View] -
goldnike:
<
[URL=http://www.goldnikevip.com/]Nike Sneaker...
[View] -
müzik dinle:
<
HidayetSohbeti.netmüzik dinlethanks!..
[View] -
müzik dinle:
<
HidayetSohbeti.netmüzik dinlethanks!..
[View] -
goldnike:
<
[URL=http://www.goldnikevip.com/]Nike Sneaker...
[View] -
goldnike:
<
[URL=http://www.goldnikevip.com/]Nike Sneaker...
[View]
Other Stuff
    |
       |
|
 |