Postgres Pro
Downloads
Home   >   mailing lists

Thread: length limitation in '=' operator when comparing TEXT fields ?

length limitation in '=' operator when comparing TEXT fields ?

From
"Michael Beckstette"
Date:
Hi,

i have a little problem when selecting entries from a table, specified by a
 long match on a field of type TEXT. Is there a length limitation for the '='
operator when comparing a field of type TEXT to a given string ?

Example:

SELECT description FROM sequences_33 WHERE description LIKE 'gi 17559974%';

Result:

 gi 17559974 ref NP_506864.1  (NM_074463) acid phosphatase [Caenorhabditis
elega
ns] gi 7499536 pir  T21181 hypothetical protein F21A3.2 - Caenorhabditis
elegans
 gi 3876155 emb CAB04153.1  (Z81509) predicted using Genefinder Similarity to
As
pergillus acid phosphatase (TR:Q12546) cDNA EST yk3g4.5 comes from this gene
cDN
A EST yk69f10.5 comes from this gene cDNA EST yk231b3.5 comes from this gene
cDN
A EST yk285e4.5 comes from this gene cDN
(1 row)


Thats ok. And now:

SELECT description FROM sequences_33 WHERE description=' gi 17559974 ref
NP_506864.1  (NM_074463) acid phosphatase [Caenorhabditis elega
ns] gi 7499536 pir  T21181 hypothetical protein F21A3.2 - Caenorhabditis
elegans
 gi 3876155 emb CAB04153.1  (Z81509) predicted using Genefinder Similarity to
As
pergillus acid phosphatase (TR:Q12546) cDNA EST yk3g4.5 comes from this gene
cDN
A EST yk69f10.5 comes from this gene cDNA EST yk231b3.5 comes from this gene
cDN
A EST yk285e4.5 comes from this gene cDN'
(0 row)

The two strings are identical and there are no confusing characters in them.

Any idea ?

PS: The type of field description is TEXT

Thanx in advance

Michael

-------------------------------------------------------------------------------
Dipl.-Inform. Michael Beckstette        Office: M3-129
AG-PI / Technische Fakultaet            EMail:mbeckste@techfak.uni-bielefeld.de
Universitaet Bielefeld                  Fon: +49-521-106-2914
Postfach 100131                         Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany

Re: length limitation in '=' operator when comparing TEXT fields ?

From
Tom Lane
Date:
"Michael Beckstette" <mbeckste@TechFak.Uni-Bielefeld.DE> writes:
> i have a little problem when selecting entries from a table, specified by a
>  long match on a field of type TEXT. Is there a length limitation for the '='
> operator when comparing a field of type TEXT to a given string ?

No.

> Example:

> SELECT description FROM sequences_33 WHERE description LIKE 'gi 17559974%';
                                                         ^^^^^^^^^^^^^^^^^^^
> [ finds a row ]

> SELECT description FROM sequences_33 WHERE description=' gi 17559974 ref
                                                        ^^^^^^^^^^^^^^^^^
> [ finds no row ]

You seem to think that leading spaces are insignificant.  They are not.

            regards, tom lane

Re: length limitation in '=' operator when comparing TEXT fields ?

From
"Michael Beckstette"
Date:
Hi,
ok my fault. It was a cut and paste error, there is no leading blank. In my
view this only happens when the strings to compare are quite "long".
If there is no limitation for the '=' operator itself,could it be possible that
something like this happens when the query is passed through LIPQ's PQexec
function ?

My system:
PostgreSQL 7.1.2 on sparc-sun-solaris2.5.1, compiled by GCC 2.95

Regards

Michael Beckstette