Re: between

Is there any chance on supporting BETWEEN in the WHERE conditions of a SELECT
clause?
Isn't that in the SQL92 or SQL3?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

> Is there any chance on supporting BETWEEN in the WHERE conditions of a SELECT
> clause?

You mean like this?

Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

Timing is on.
uscf=> select count(*) from memmast
uscf-> where memid between '10000000' and '19999999';
count
--------
514070
(1 row)

That's been in PostgreSQL for a long time.
--
Mike Nolan

El Vie 26 Dic 2003 13:02, Mike Nolan escribió:
> > Is there any chance on supporting BETWEEN in the WHERE conditions of a
SELECT
> > clause?
>
> You mean like this?
>
> Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> Timing is on.
> uscf=> select count(*) from memmast
> uscf-> where memid between '10000000' and '19999999';
> count
> --------
> 514070
> (1 row)
>
> That's been in PostgreSQL for a long time.

Damn, didn't find it in the docs, and just supposed it wasn't there.

Well, shouldn't there be something writen especially in the SELECT manual?

http://www.postgresql.org/docs/current/interactive/sql-select.html

Sorry for not checking it out before. :-(

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:
>> That's been in PostgreSQL for a long time.

> Damn, didn't find it in the docs, and just supposed it wasn't there.

It's been shown on the 'Comparison Operators' page for a long time:

http://www.postgresql.org/docs/7.1/static/functions-comparison.html

> Well, shouldn't there be something writen especially in the SELECT manual?

It'd be fairly unhelpful to try to mention every available operator on
SELECT's reference page, I would think.

regards, tom lane

El Vie 26 Dic 2003 16:26, escribió:
> Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:
> >> That's been in PostgreSQL for a long time.
>
> > Damn, didn't find it in the docs, and just supposed it wasn't there.
>
> It's been shown on the 'Comparison Operators' page for a long time:
>
> http://www.postgresql.org/docs/7.1/static/functions-comparison.html
>
> > Well, shouldn't there be something writen especially in the SELECT manual?
>
> It'd be fairly unhelpful to try to mention every available operator on
> SELECT's reference page, I would think.

A link to the URL above in the SELECT page?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

> Damn, didn't find it in the docs, and just supposed it wasn't there.

Try finding it in the online mysql docs. :-)

Yes, it is there, but it took me far longer to wade through their
docs to find it than in the postgresql docs.

It took me less time with the Oracle SQL Language Reference Manual, but
I cheated by looking it up in the index. There are index entries for
'BETWEEN' in "Practical PosgreSQL" and "Managing & Using MySQL", too.

Sometimes books are still better than online docs. :-)
--
Mike Nolan

El Vie 26 Dic 2003 18:02, Mike Nolan escribió:
> > Damn, didn't find it in the docs, and just supposed it wasn't there.
>
> Try finding it in the online mysql docs. :-)
>
> Yes, it is there, but it took me far longer to wade through their
> docs to find it than in the postgresql docs.
>
> It took me less time with the Oracle SQL Language Reference Manual, but
> I cheated by looking it up in the index. There are index entries for
> 'BETWEEN' in "Practical PosgreSQL" and "Managing & Using MySQL", too.
>
> Sometimes books are still better than online docs. :-)

How dificult would it be to build an index from a SGML archive? :-)

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Mike Nolan <nolan(at)gw(dot)tssi(dot)com> writes:
> It took me less time with the Oracle SQL Language Reference Manual, but
> I cheated by looking it up in the index.

Just FYI, there's an index entry for BETWEEN in the PG docs too:
http://www.postgresql.org/docs/7.4/static/bookindex.html
although it seems to mistakenly be lowercase instead of uppercase
as one would expect.

In general though I agree that the indexing of the docs is pretty
weak. Perhaps someone would care to step up and submit docs patches
to improve the situation? Adding index entries is no sweat if you
have even a moderate acquaintance with SGML or HTML ... we just need
someone willing to go through the files and add suitable entries ...

regards, tom lane

El Vie 26 Dic 2003 18:27, Tom Lane escribió:
> Mike Nolan <nolan(at)gw(dot)tssi(dot)com> writes:
> > It took me less time with the Oracle SQL Language Reference Manual, but
> > I cheated by looking it up in the index.
>
> Just FYI, there's an index entry for BETWEEN in the PG docs too:
> http://www.postgresql.org/docs/7.4/static/bookindex.html

Once again, I was wrong. OK, bookmarked for the future. :-)

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

> Just FYI, there's an index entry for BETWEEN in the PG docs too:
> http://www.postgresql.org/docs/7.4/static/bookindex.html
> although it seems to mistakenly be lowercase instead of uppercase
> as one would expect.

I think I tried searching on 'between' but didn't find anything.

> In general though I agree that the indexing of the docs is pretty
> weak. Perhaps someone would care to step up and submit docs patches
> to improve the situation? Adding index entries is no sweat if you
> have even a moderate acquaintance with SGML or HTML ... we just need
> someone willing to go through the files and add suitable entries ...

I may look into it after the first of the year, though I'm likely to
propose something more sweeping than that.
--
Mike Nolan

>>>>> "Martin" == Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:

Martin> A link to the URL above in the SELECT page?

Uh, do you also want a link to "installing PostgreSQL" there too?
After all, you have to install Pg before you can use the SELECT
operator.

It's not any more related to SELECT than it is anything else. It's a
part of an expression. Expressions are used many places and described
one place. That's the nature of documentation.

Maybe I'm sounding grouchy, but at some point, you do the common sense
thing.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

El Vie 26 Dic 2003 20:10, Randal L. Schwartz escribió:
> >>>>> "Martin" == Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:
>
> Martin> A link to the URL above in the SELECT page?
>
> Uh, do you also want a link to "installing PostgreSQL" there too?
> After all, you have to install Pg before you can use the SELECT
> operator.

Please, don't get me wrong, I have read great part on the PG manuals already,
and have mad lots of Solaris instalation in times of the early PG 7.0, when
there was not much experience on this OS, and have been trying to make a 64
bit build on Linux/SPARC.

Do you think I would have been able to do this without reading "installing
PostgreSQL"? :-)

> It's not any more related to SELECT than it is anything else. It's a
> part of an expression. Expressions are used many places and described
> one place. That's the nature of documentation.
>
> Maybe I'm sounding grouchy, but at some point, you do the common sense
> thing.

I personaly find this incomplete:

==================>CUT FROM THE SELECT MANUAL<===========================
WHERE Clause

The optional WHERE condition has the general form:

WHERE boolean_expr

boolean_expr can consist of any expression which evaluates to a Boolean value.
In many cases, this expression will be:

expr cond_op expr

or

log_op expr

where cond_op can be one of: =, <, <=, >, >= or <>, a conditional operator
like ALL, ANY, IN, LIKE, or a locally defined operator, and log_op can be one
of: AND, OR, NOT. SELECT will ignore all rows for which the WHERE condition
does not return TRUE.
==================>CUT FROM THE SELECT MANUAL<===========================

Now in this last paragraph there are some comparision operators. How awful
could it be to just add the word "BETWEEN" there? I know the BETWEEN sintax,
but as I didn't see it there I thought, wrongly, that it wasn't supported.

Maybe it's that I have the bad habit of just reading the "SQL Commands" while
I work, and having a very volatile memory. :-)

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Martin Marques <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:
> I personaly find this incomplete:
> ==================>CUT FROM THE SELECT MANUAL<===========================

So it is, but I think the solution is to eliminate the incomplete
examples. Which, indeed, seems to have been done in the 7.4 docs:
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-WHERE
http://www.postgresql.org/docs/7.4/static/sql-select.html

regards, tom lane

Mike Nolan (Friday 26 December 2003 16:02)
> Sometimes books are still better than online docs. :-)

No, the online docs simply need an index with links :-). If I could look at a
complete index and quickly find BETWEEN in the B's with links to applicable
sections, I would have no other wish of the already very good documentation.

Vertu sæll,

--
Sigþór Björn Jarðarson (Casey Allen Shobe)
cshobe(at)softhome(dot)net / http://rivyn.livejournal.com
Jabber: sigthor(at)jabber(dot)org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy

Free development contributor of:
> KDE toolbar icons
> Kopete user interface, usability, and testing
> X11 Icelandic Dvorak keymaps
> Reporting of over 100 Kopete bugs

Tom Lane (Friday 26 December 2003 16:27)
> Just FYI, there's an index entry for BETWEEN in the PG docs too:
> http://www.postgresql.org/docs/7.4/static/bookindex.html
> although it seems to mistakenly be lowercase instead of uppercase
> as one would expect.

Oh! There is an index now. Wonderful!

> In general though I agree that the indexing of the docs is pretty
> weak.

Yeah...not exactly what I'd call a *complete* index, but still better than
what I thought existed (no index).

*smacks self in forehead for previous post to mailing list complaining about
no index*
(http://199.72.170.146/~sigthor/images/humor/stupid.jpeg)

Vertu sæll,

--
Sigþór Björn Jarðarson (Casey Allen Shobe)
cshobe(at)softhome(dot)net / http://rivyn.livejournal.com
Jabber: sigthor(at)jabber(dot)org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy

Free development contributor of:
> KDE toolbar icons
> Kopete user interface, usability, and testing
> X11 Icelandic Dvorak keymaps
> Reporting of over 100 Kopete bugs