Thread: index not used in ( varchar = text )

Hi all,

why in Postgresql 7.3.2 in a select like:

SELECT *
FROM user_login
WHERE login = 'FOO'::text;

and login is a VARCHAR the index on login
is not used anymore ?


Ciao
Gaetano

Why would you want to cast that to text, if your field is a varchar?  I
wouldn't think you would have to cast it at all, or if you do, cast it
to a varchar.

Curious,

WIll


On Sat, 2003-02-22 at 10:55, Gaetano Mendola wrote:
> Hi all,
>
> why in Postgresql 7.3.2 in a select like:
>
> SELECT *
> FROM user_login
> WHERE login = 'FOO'::text;
>
> and login is a VARCHAR the index on login
> is not used anymore ?
>
>
> Ciao
> Gaetano
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

----- Original Message -----
From: "Will LaShell" <will@lashell.net>
Newsgroups: comp.databases.postgresql.admin
Sent: Sunday, February 23, 2003 7:02 PM
Subject: Re: index not used in ( varchar = text )


> Why would you want to cast that to text, if your field is a varchar?  I
> wouldn't think you would have to cast it at all, or if you do, cast it
> to a varchar.
>
> Curious,
>
> WIll


Of course that example was a semplification of a real
situation:


..........
my_login ALIAS FOR $1;


SELECT *
FROM user_login
WHERE login = my_login;
..................


and $1 is of type TEXT.

I had the same function on Postgres 7.2.3 and
was working correctly.


Ciao
Gaetano

"Gaetano Mendola" <mendola@bigfoot.com> writes:
>> Why would you want to cast that to text, if your field is a varchar?

> Of course that example was a semplification of a real
> situation:
> ..........
> my_login ALIAS FOR $1;

> SELECT *
> FROM user_login
> WHERE login = my_login;

> and $1 is of type TEXT.

So declare it as VARCHAR.  Or cast it to varchar.  You need to get rid
of the datatype ambiguity.  "text = text" is a different operator from
"varchar = varchar".

            regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Newsgroups: comp.databases.postgresql.admin
Sent: Monday, February 24, 2003 2:57 AM
Subject: Re: index not used in ( varchar = text )


> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> >> Why would you want to cast that to text, if your field is a varchar?
>
> > Of course that example was a semplification of a real
> > situation:
> > ..........
> > my_login ALIAS FOR $1;
>
> > SELECT *
> > FROM user_login
> > WHERE login = my_login;
>
> > and $1 is of type TEXT.
>
> So declare it as VARCHAR.  Or cast it to varchar.

I did, and fortunatelly I found this behaviour before
to upgrade the production database.

> You need to get rid
> of the datatype ambiguity.  "text = text" is a different operator from
> "varchar = varchar".


Yes I agree but was not like this in the Postgres 7.2.X version


Ciao
Gaetano.
html

"Gaetano Mendola" <mendola@bigfoot.com> writes:
>> You need to get rid
>> of the datatype ambiguity.  "text = text" is a different operator from
>> "varchar = varchar".

> Yes I agree but was not like this in the Postgres 7.2.X version

That was a bug, or at least a horridly misdesigned behavior.  The
operator chosen by the parser is "text = text" (because text is the
preferred datatype in the string class).  Leaving aside any issues
about whether the parser's choice is correct, it is surely no business
of the planner to replace that choice with "varchar = varchar" ---
but that's what it was doing.  The folly of this idea can be seen by
realizing that the same code would equally happily replace it with
"char = char", which has distinctly different semantics.

            regards, tom lane