Re: Using MS Access front-end with PG]

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

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

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             :                 :
+---------------------------------------+-----------------+

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 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