Postgres Pro
Downloads
Home   >   mailing lists

Thread: > and >= give the same result

> and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
Hi list,

I'm trying to help a friend with his queries and I've something
strange (well, to me:):

SELECT * FROM tb1 WHERE note > 'y';

returns the *same* result as:

SELECT * FROM tb1 WHERE note >= 'y';

There's something wrong: I though I'd have:
1st qry: z%
2nd qry: y% and z%

What is wrong with using '>' &| '>=' with strings?

I also know there's a page talking about that in the docs, but I
can't find it.

JY
--
Help Stamp Out Rape!  (Say Yes.)

Re: > and >= give the same result

From
Frank Bax
Date:
On 12/29/11 08:15, Jean-Yves F. Barbier wrote:
> Hi list,
>
> I'm trying to help a friend with his queries and I've something
> strange (well, to me:):
>
> SELECT * FROM tb1 WHERE note>  'y';
>
> returns the *same* result as:
>
> SELECT * FROM tb1 WHERE note>= 'y';
>
> There's something wrong: I though I'd have:
> 1st qry: z%
> 2nd qry: y% and z%
>
> What is wrong with using '>'&| '>=' with strings?
>
> I also know there's a page talking about that in the docs, but I
> can't find it.


The string 'y%' is greater than 'y'! Therefore, both queries will return
y% and z% - where is the problem?

Re: > and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
On Thu, 29 Dec 2011 08:48:37 -0500
Frank Bax <fbax@sympatico.ca> wrote:

>
> The string 'y%' is greater than 'y'! Therefore, both queries will return
> y% and z% - where is the problem?

Well, I hoped '>' would have returned all rows beginning by 'z'
and not any 'y'.

--
Love sometimes expresses itself in sacrifice.
        -- Kirk, "Metamorphosis", stardate 3220.3

Re: > and >= give the same result

From
Frank Bax
Date:
On 12/29/11 09:29, Jean-Yves F. Barbier wrote:
> On Thu, 29 Dec 2011 08:48:37 -0500
> Frank Bax<fbax@sympatico.ca>  wrote:
>>
>> The string 'y%' is greater than 'y'! Therefore, both queries will return
>> y% and z% - where is the problem?
>
> Well, I hoped '>' would have returned all rows beginning by 'z'
> and not any 'y'.


WHERE substr(note,0,1)>'y'

Re: > and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
On Thu, 29 Dec 2011 08:48:37 -0500
Frank Bax <fbax@sympatico.ca> wrote:

>
> The string 'y%' is greater than 'y'! Therefore, both queries will return
> y% and z% - where is the problem?

Oops, I'm read too fast; but it dont change my PB:
SELECT * FROM table WHERE col > 'Y%' still returns y% & Y%

--
Repartee is something we think of twenty-four hours too late.
        -- Mark Twain

Re: > and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
On Thu, 29 Dec 2011 10:16:53 -0500
Frank Bax <fbax@sympatico.ca> wrote:

> On 12/29/11 09:29, Jean-Yves F. Barbier wrote:
> > On Thu, 29 Dec 2011 08:48:37 -0500
> > Frank Bax<fbax@sympatico.ca>  wrote:
> >>
> >> The string 'y%' is greater than 'y'! Therefore, both queries will return
> >> y% and z% - where is the problem?
> >
> > Well, I hoped '>' would have returned all rows beginning by 'z'
> > and not any 'y'.
>
>
> WHERE substr(note,0,1)>'y'

Nope:

comgest=> SELECT * FROM tst1m WHERE substr(note,0,1) > 'y' ORDER BY name;
 id | name | note
----+------+------
(0 ligne)

comgest=> SELECT * FROM tst1m WHERE substr(note,0,1) > 'y%' ORDER BY name;
 id | name | note
----+------+------
(0 ligne)

comgest=> SELECT * FROM tst1m WHERE note ILIKE 'z%' ORDER BY name;
...
(2567 lignes)

--
PUNK ROCK!!  DISCO DUCK!!  BIRTH CONTROL!!

Re: > and >= give the same result

From
Frank Bax
Date:
On 12/29/11 10:19, Jean-Yves F. Barbier wrote:
> On Thu, 29 Dec 2011 08:48:37 -0500
> Frank Bax<fbax@sympatico.ca>  wrote:
>
>>
>> The string 'y%' is greater than 'y'! Therefore, both queries will return
>> y% and z% - where is the problem?
>
> Oops, I'm read too fast; but it dont change my PB:
> SELECT * FROM table WHERE col>  'Y%' still returns y%&  Y%
>

NO.  It does not.

create table barbier (col varchar);
insert into barbier values ('y%');
insert into barbier values ('z%');
insert into barbier values ('Y%');
  select * from barbier where col>'Y%';
  col
-----
  y%
  z%
(2 rows)

Re: > and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
On Thu, 29 Dec 2011 10:39:41 -0500
Frank Bax <fbax@sympatico.ca> wrote:

> > Oops, I'm read too fast; but it dont change my PB:
> > SELECT * FROM table WHERE col>  'Y%' still returns y%&  Y%
> >
>
> NO.  It does not.
>
> create table barbier (col varchar);
> insert into barbier values ('y%');
> insert into barbier values ('z%');
> insert into barbier values ('Y%');
>   select * from barbier where col>'Y%';
>   col
> -----
>   y%
>   z%
> (2 rows)

Do you mean that>'Y%' is taken as a literal and don't
develop as Y* (contrary to LIKE 'Y%')?

Meaning 'Y%' was for me: 'Yahoo', 'Ybsd', 'Ycommerce', 'Ymail', etc

--
Damn, I need a Coke!
        -- Dr. William DeVries
        [after implanting the first artificial human heart]

Re: > and >= give the same result

From
Thomas Kellerer
Date:
Jean-Yves F. Barbier wrote on 29.12.2011 17:04:
> Do you mean that>'Y%' is taken as a literal and don't
> develop as Y* (contrary to LIKE 'Y%')?
>
> Meaning 'Y%' was for me: 'Yahoo', 'Ybsd', 'Ycommerce', 'Ymail', etc
>

% is only a wildcard when used with the LIKE operator.

Re: > and >= give the same result

From
"Jean-Yves F. Barbier"
Date:
On Thu, 29 Dec 2011 17:17:08 +0100
Thomas Kellerer <spam_eater@gmx.net> wrote:

>
> % is only a wildcard when used with the LIKE operator.

Haaaa, now I understand the results, thanks Thomas!

--
A man who cannot seduce men cannot save them either.
        -- Soren Kierkegaard