By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

between

P: n/a
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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
> 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
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/curre...ql-select.html

Sorry for not checking it out before. :-(

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

P: n/a
Martin Marques <ma****@bugs.unl.edu.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/s...omparison.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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a
El Vie 26 Dic 2003 16:26, escribió:
Martin Marques <ma****@bugs.unl.edu.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/s...omparison.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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #5

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #6

P: n/a
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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #7

P: n/a
Mike Nolan <no***@gw.tssi.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/s...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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #8

P: n/a
El Vie 26 Dic 2003 18:27, Tom Lane escribió:
Mike Nolan <no***@gw.tssi.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/s...bookindex.html


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

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #9

P: n/a
> Just FYI, there's an index entry for BETWEEN in the PG docs too:
http://www.postgresql.org/docs/7.4/s...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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #10

P: n/a
>>>>> "Martin" == Martin Marques <ma****@bugs.unl.edu.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
<me****@stonehenge.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!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #11

P: n/a
El Vie 26 Dic 2003 20:10, Randal L. Schwartz escribió:
>> "Martin" == Martin Marques <ma****@bugs.unl.edu.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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #12

P: n/a
Martin Marques <ma****@bugs.unl.edu.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/s...#QUERIES-WHERE
http://www.postgresql.org/docs/7.4/s...ql-select.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #13

P: n/a
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)
cs****@softhome.net / http://rivyn.livejournal.com
Jabber: si*****@jabber.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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #14

P: n/a
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/s...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)
cs****@softhome.net / http://rivyn.livejournal.com
Jabber: si*****@jabber.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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.