Thread: Re: Using MS Access front-end with PG]
Tom Lane wrote: > Paul Lambert <paul.lambert@autoledgers.com.au> writes: >> Is there any way to change the text qualifier in PG > > No. I suppose you could hack the Postgres lexer but you'd break > pretty much absolutely everything other than your Access code. > >> or the case sensitivity? > > That could be attacked in a few ways, depending on whether you want > all text comparisons to be case-insensitive or only some (and if so > which "some"). But it sounds like MS SQL's backward standards for > strings vs identifiers has got you nicely locked in, as intended :-( > so there may be no point in discussing further. I don't have any case sensitive data - so if sensitivity could be completely disabled by a parameter somewhere, that would be nice. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert wrote:
>
>
> Tom Lane wrote:
>> Paul Lambert <paul.lambert@autoledgers.com.au> writes:
>>> Is there any way to change the text qualifier in PG
>>
>> No. I suppose you could hack the Postgres lexer but you'd break
>> pretty much absolutely everything other than your Access code.
>>
>>> or the case sensitivity?
>>
>> That could be attacked in a few ways, depending on whether you want
>> all text comparisons to be case-insensitive or only some (and if so
>> which "some"). But it sounds like MS SQL's backward standards for
>> strings vs identifiers has got you nicely locked in, as intended :-(
>> so there may be no point in discussing further.
>
> I don't have any case sensitive data - so if sensitivity could be
> completely disabled by a parameter somewhere, that would be nice.
You could preface all your queries with something like:
select * from foo where lower(bar) = lower('qualifer');
But that seems a bit silly.
Joshua D. Drake
>
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote:
> You could preface all your queries with something like:
>
> select * from foo where lower(bar) = lower('qualifer');
>
> But that seems a bit silly.
>
>
And also it would prevent the optimizer from using any indexes on
"bar". Not a good idea.
Eddy
On Tue, 03 Apr 2007 18:24:00 -0700, "Joshua D. Drake" <jd@commandprompt.com> wrote:
> Paul Lambert wrote:
> > Tom Lane wrote:
> >> Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> >>> or the case sensitivity?
> >>
> >> That could be attacked in a few ways, depending on whether you want
> >> all text comparisons to be case-insensitive or only some (and if so
> >> which "some"). But it sounds like MS SQL's backward standards for
> >> strings vs identifiers has got you nicely locked in, as intended :-(
> >> so there may be no point in discussing further.
> >
> > I don't have any case sensitive data - so if sensitivity could be
> > completely disabled by a parameter somewhere, that would be nice.
>
> You could preface all your queries with something like:
>
> select * from foo where lower(bar) = lower('qualifer');
>
> But that seems a bit silly.
Is there any way to create operators to point like to ilike? There
doesn't seem to be a like or ilike in pg_operator (not in 7.4 anyway).
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
Joshua D. Drake wrote:
>
> You could preface all your queries with something like:
>
> select * from foo where lower(bar) = lower('qualifer');
>
> But that seems a bit silly.
>
> Joshua D. Drake
>
>
I'm trying to avoid having to alter all of my queries, per the OP I've
got several hundred if not thousands of them and if I have to change
them all to put lower() around all the text, that is a lot of time.
If I have to do that I will, I'm just curious if there was an ability to
tell pg to not be case sensitive when doing lookups.
Judging by the responses so far, there is not... so I'll get to work :)
--
Paul Lambert
Database Administrator
AutoLedgers
Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> Tom Lane wrote:
>> That could be attacked in a few ways, depending on whether you want
>> all text comparisons to be case-insensitive or only some (and if so
>> which "some").
> I don't have any case sensitive data - so if sensitivity could be
> completely disabled by a parameter somewhere, that would be nice.
If you are certain of that, the best way would be to initdb in a
case-insensitive locale setting. My locale-fu is insufficient to
tell you exactly how to create a case-insensitive locale if you
haven't got one already, but I believe it is possible. One note
is to be sure that the locale uses the character encoding you want
to use.
regards, tom lane
Edward Macnaghten wrote:
> Joshua D. Drake wrote:
>> You could preface all your queries with something like:
>>
>> select * from foo where lower(bar) = lower('qualifer');
>>
>> But that seems a bit silly.
>>
>>
> And also it would prevent the optimizer from using any indexes on
> "bar". Not a good idea.
You could use a functional index to solve that.
CREATE INDEX lower_bar_idx on foo(lower(bar));
>
> Eddy
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Klint Gore <kg@kgb.une.edu.au> writes:
> Is there any way to create operators to point like to ilike? There
> doesn't seem to be a like or ilike in pg_operator (not in 7.4 anyway).
Actually it's the other way 'round: if you look into gram.y you'll see
that LIKE is expanded as the operator ~~ and ILIKE as the operator ~~*
... so one of the alternatives I was thinking of offering to Paul was
to rename those two operators to swap 'em. However I'm afraid that
that would break the planner, which has some hardwired assumptions
about the behavior of those two operator OIDs. Maybe we should change
the planner to look a level deeper and see what functions the operators
refer to.
regards, tom lane
Paul, we have contrib module mchar, which does what you need. We developed it when porting from MS SQL one very popular in Russia accounting software. It's available from http://v8.1c.ru/overview/postgres_patches_notes.htm, in Russian. I don't rememeber about license, though. Oleg On Wed, 4 Apr 2007, Paul Lambert wrote: > Joshua D. Drake wrote: > >> >> You could preface all your queries with something like: >> >> select * from foo where lower(bar) = lower('qualifer'); >> >> But that seems a bit silly. >> >> Joshua D. Drake >> >> > > I'm trying to avoid having to alter all of my queries, per the OP I've got > several hundred if not thousands of them and if I have to change them all to > put lower() around all the text, that is a lot of time. > > If I have to do that I will, I'm just curious if there was an ability to tell > pg to not be case sensitive when doing lookups. > > Judging by the responses so far, there is not... so I'll get to work :) > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
Oleg, This looks like a great module, do you have a pointer to it in English? If can send this module to me as a compressed file, I'll take the time to post it on PgFoundry as a new project that everyone can easily access and download. Paul- if you go with the lower() edits route, be sure to note Joshua's coment on the funcional index- one of the best, unknown features in PG. On 4/3/07, Oleg Bartunov <oleg@sai.msu.su> wrote: > Paul, > > we have contrib module mchar, which does what you need. We developed it > when porting from MS SQL one very popular in Russia accounting software. > It's available from http://v8.1c.ru/overview/postgres_patches_notes.htm, > in Russian. I don't rememeber about license, though. > > > Oleg > > On Wed, 4 Apr 2007, Paul Lambert wrote: > > > Joshua D. Drake wrote: > > > >> > >> You could preface all your queries with something like: > >> > >> select * from foo where lower(bar) = lower('qualifer'); > >> > >> But that seems a bit silly. > >> > >> Joshua D. Drake > >> > >> > > > > I'm trying to avoid having to alter all of my queries, per the OP I've got > > several hundred if not thousands of them and if I have to change them all to > > put lower() around all the text, that is a lot of time. > > > > If I have to do that I will, I'm just curious if there was an ability to tell > > pg to not be case sensitive when doing lookups. > > > > Judging by the responses so far, there is not... so I'll get to work :) > > > > > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Wed, 4 Apr 2007, Postgres User wrote: > Oleg, > > This looks like a great module, do you have a pointer to it in English? unfortunately, no. > > If can send this module to me as a compressed file, I'll take the time > to post it on PgFoundry as a new project that everyone can easily > access and download. it can not be a contrib module, since index support for LIKE requires core patching. Just download http://v8.1c.ru/overview/postgresql_patches/1c_FULL_81-0.11.patch, it contains contrib/mchar module. > > Paul- if you go with the lower() edits route, be sure to note Joshua's > coment on the funcional index- one of the best, unknown features in > PG. > > On 4/3/07, Oleg Bartunov <oleg@sai.msu.su> wrote: >> Paul, >> >> we have contrib module mchar, which does what you need. We developed it >> when porting from MS SQL one very popular in Russia accounting software. >> It's available from http://v8.1c.ru/overview/postgres_patches_notes.htm, >> in Russian. I don't rememeber about license, though. >> >> >> Oleg >> >> On Wed, 4 Apr 2007, Paul Lambert wrote: >> >> > Joshua D. Drake wrote: >> > >> >> >> >> You could preface all your queries with something like: >> >> >> >> select * from foo where lower(bar) = lower('qualifer'); >> >> >> >> But that seems a bit silly. >> >> >> >> Joshua D. Drake >> >> >> >> >> > >> > I'm trying to avoid having to alter all of my queries, per the OP I've >> got >> > several hundred if not thousands of them and if I have to change them all >> to >> > put lower() around all the text, that is a lot of time. >> > >> > If I have to do that I will, I'm just curious if there was an ability to >> tell >> > pg to not be case sensitive when doing lookups. >> > >> > Judging by the responses so far, there is not... so I'll get to work :) >> > >> > >> >> Regards, >> Oleg >> _____________________________________________________________ >> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), >> Sternberg Astronomical Institute, Moscow University, Russia >> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ >> phone: +007(495)939-16-83, +007(495)939-23-83 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83