Thread: Case sensitivity
We intend to move from M$ Access to PostgreSQL, however, much of our legacy code operates under the assumption that string or text comparisons are case insensitive whereas postgres is case sensitive. This issue extends to SQL Select statements as well as straight comparison (i.e. in addition to the <, > and = operators, the WHERE and IN clauses in SQL statements are also issues) so we can't simply modify the function attached to the comparison operators. Is there some means of modifying the behaviour of PostgreSQL so that it is case insensitive?
Attachment
Hello Scott,
PostgreSQL also supports regular expressions, _VERY_ useful..;-)
~* regular expression, case-insensitive..;-)
Try to get a copy of Bruce's book
Introduction and Concepts
by Bruce Momjian
A sample from the book:
SELECT * FROM friend WHERE firstname ~* '[bc]'
read it online, or just read the docs online...;-)
http://techdocs.postgresql.org
Cheers,
John Clark
p.s. don't change PostgreSQL, change your SQL statements...;-)
Scott Gilbert wrote:
>
> We intend to move from M$ Access to PostgreSQL, however, much of our legacy
> code operates under the assumption that string or text comparisons are case
> insensitive whereas postgres is case sensitive. This issue extends to SQL
> Select statements as well as straight comparison (i.e. in addition to the <,
> > and = operators, the WHERE and IN clauses in SQL statements are also
> issues) so we can't simply modify the function attached to the comparison
> operators. Is there some means of modifying the behaviour of PostgreSQL so
> that it is case insensitive?
--
/) John Clark Naldoza y Lopez (\
/ ) Software Design Engineer III ( \
_( (_ _ Web-Application Development _) )_
(((\ \> /_> Cable Modem Network Management System <_\ </ /)))
(\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////)
\ / \ /
\ _/ phone: (+63 32) 233-9142 loc. 3113 \_ /
/ / cellphone: (+63 919) 399-4742 \ \
/ / email: njclark@ntsp.nec.co.jp \ \
"Intelligence is the ability to avoid doing work, yet getting the work
done"
--Linus Torvalds
Very good book. I have the .pdf file of it from before they took it off the site. John Clark Naldoza y Lopez wrote: > > Hello Scott, > > PostgreSQL also supports regular expressions, _VERY_ useful..;-) > > ~* regular expression, case-insensitive..;-) > > Try to get a copy of Bruce's book > > Introduction and Concepts > by Bruce Momjian > > A sample from the book: > SELECT * FROM friend WHERE firstname ~* '[bc]' > > read it online, or just read the docs online...;-) > > http://techdocs.postgresql.org > > Cheers, > > John Clark > > p.s. don't change PostgreSQL, change your SQL statements...;-) > > Scott Gilbert wrote: > > > > We intend to move from M$ Access to PostgreSQL, however, much of our legacy > > code operates under the assumption that string or text comparisons are case > > insensitive whereas postgres is case sensitive. This issue extends to SQL > > Select statements as well as straight comparison (i.e. in addition to the <, > > > and = operators, the WHERE and IN clauses in SQL statements are also > > issues) so we can't simply modify the function attached to the comparison > > operators. Is there some means of modifying the behaviour of PostgreSQL so > > that it is case insensitive? > > -- > /) John Clark Naldoza y Lopez (\ > / ) Software Design Engineer III ( \ > _( (_ _ Web-Application Development _) )_ > (((\ \> /_> Cable Modem Network Management System <_\ </ /))) > (\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////) > \ / \ / > \ _/ phone: (+63 32) 233-9142 loc. 3113 \_ / > / / cellphone: (+63 919) 399-4742 \ \ > / / email: njclark@ntsp.nec.co.jp \ \ > > "Intelligence is the ability to avoid doing work, yet getting the work > done" > --Linus Torvalds > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)