BETWEEN clause

On Mon, 23 Apr 2001, Paul Tomblin wrote:

Is the "BETWEEN" clause inclusive or exclusive? ie if I say "WHERE
latitude BETWEEN 45 and 55", will I get examples where the latitude equals
45 or not? Also, is "latitude BETWEEN 45 and 55" any more efficient than
"latitude >= 45 AND latitude <= 55", or is it just a stylistic thing?

yes, yes, and no:

select 'exclusive' where 2 between 1 and 3;
?column?
----------
inclusive

test=# select 'inclusive' where 1 between 1 and 3;
?column?
----------
inclusive

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Tue, Apr 24, 2001 at 12:07:41AM -0400, Joel Burton wrote:

On Mon, 23 Apr 2001, Paul Tomblin wrote:

Is the "BETWEEN" clause inclusive or exclusive? ie if I say "WHERE
latitude BETWEEN 45 and 55", will I get examples where the latitude equals
45 or not? Also, is "latitude BETWEEN 45 and 55" any more efficient than
"latitude >= 45 AND latitude <= 55", or is it just a stylistic thing?

yes, yes, and no:

select 'exclusive' where 2 between 1 and 3;
?column?
----------
inclusive

test=# select 'inclusive' where 1 between 1 and 3;
?column?
----------
inclusive

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

just wanted to say -- BEAUTIFULLY executed reply.
not only did you answer the query succinctly and completely,
you showed, quite clearly, how to find out such answers.

"teach a man to fish..."

delightful!

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Quoting will trillich (will@serensoft.com):

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

just wanted to say -- BEAUTIFULLY executed reply.
not only did you answer the query succinctly and completely,
you showed, quite clearly, how to find out such answers.

I was impressed as well. I didn't know you could use \d to find the
definition of views like that.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"Nobody can be told what the dominatrix is, they have to see it for themselves"

On Tue, 24 Apr 2001, Paul Tomblin wrote:

Quoting will trillich (will@serensoft.com):

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

just wanted to say -- BEAUTIFULLY executed reply.
not only did you answer the query succinctly and completely,
you showed, quite clearly, how to find out such answers.

I was impressed as well. I didn't know you could use \d to find the
definition of views like that.

Yep.

Now what I'd love is \recreate foobar which would execute

DROP VIEW foobar; CREATE VIEW foobar AS ...

So that I could conveniently up-arrow in psql's readline, and edit and
re-create the view.

Woud save me a hundred vi fumblings or X-mouse cutting and pastings a
week.

... who needs GUIs? ;-)

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Tue, Apr 24, 2001 at 07:50:17AM -0400, Paul Tomblin wrote:

Quoting will trillich (will@serensoft.com):

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

just wanted to say -- BEAUTIFULLY executed reply.
not only did you answer the query succinctly and completely,
you showed, quite clearly, how to find out such answers.

I was impressed as well. I didn't know you could use \d to find the
definition of views like that.

i finally figured that one out after my ten-thousandth "\?" where
i saw \d*:

[snip]
\copyright show PostgreSQL usage and distribution terms
\d <table> describe table (or view, index, sequence)
\d{t|i|s|v} list tables/indices/sequences/views
\d{p|S|l} list permissions/system tables/lobjects
\da list aggregates
\dd [object] list comment for table, type, function, or operator
\df list functions
\do list operators
\dT list data types
[snip]

thus \dv shows views, \dt tables, \di indexes, \ds sequences

altho "\d view_name" shows view definition and "\dv view" shows a
list of views whose name is LIKE "view%"...

and elsewhere i saw that \d+ would show more info, albeit
not-much-used comments. (and \dv+ and \di+...)

see "\h comment" for more on comments/descriptions.
(anybody using those for anything, by the way?)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

If someone can think of a clearer way to present it, we are all ears.

On Tue, Apr 24, 2001 at 07:50:17AM -0400, Paul Tomblin wrote:

Quoting will trillich (will@serensoft.com):

test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;

test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

just wanted to say -- BEAUTIFULLY executed reply.
not only did you answer the query succinctly and completely,
you showed, quite clearly, how to find out such answers.

I was impressed as well. I didn't know you could use \d to find the
definition of views like that.

i finally figured that one out after my ten-thousandth "\?" where
i saw \d*:

[snip]
\copyright show PostgreSQL usage and distribution terms
\d <table> describe table (or view, index, sequence)
\d{t|i|s|v} list tables/indices/sequences/views
\d{p|S|l} list permissions/system tables/lobjects
\da list aggregates
\dd [object] list comment for table, type, function, or operator
\df list functions
\do list operators
\dT list data types
[snip]

thus \dv shows views, \dt tables, \di indexes, \ds sequences

altho "\d view_name" shows view definition and "\dv view" shows a
list of views whose name is LIKE "view%"...

and elsewhere i saw that \d+ would show more info, albeit
not-much-used comments. (and \dv+ and \di+...)

see "\h comment" for more on comments/descriptions.
(anybody using those for anything, by the way?)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Tue, 24 Apr 2001, Bruce Momjian wrote:

see "\h comment" for more on comments/descriptions.
(anybody using those for anything, by the way?)

I often link PG dbs to Access front ends, and have a VBA script that digs
the descriptions out of the pg_desc table to use as the Access description
(which the Access GUI automatically shows as help, etc.)

So, yes, I find them useful.

One small point I learned recently: you can used regular expressions with
the \d* commands. So, to find all functions that start with 'web_':

\df web_

to find all that *contain* web:

\df .*web_.*

Nifty!

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Quoting Bruce Momjian (pgman@candle.pha.pa.us):

If someone can think of a clearer way to present it, we are all ears.

Speaking for myself, the problem isn't that the information in \? isn't
helpful, it's that I was expecting something more like "SHOW TABLE" or
"DESCRIBE" which is how it's done on the previous couple of SQL dbmses
I've used.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"Malcolm solves his problems with a chain saw, and he never has the same
problem twice" - Arrogant Worms

On Tue, 24 Apr 2001, Paul Tomblin wrote:

Quoting Bruce Momjian (pgman@candle.pha.pa.us):

If someone can think of a clearer way to present it, we are all ears.

Speaking for myself, the problem isn't that the information in \? isn't
helpful, it's that I was expecting something more like "SHOW TABLE" or
"DESCRIBE" which is how it's done on the previous couple of SQL dbmses
I've used.

The challenge is that, for some databases, the database server itself
parses these commands, where, for PG, \d is turned into perfectly normal
PG queries to the system catalogs. (psql -E will let you see this
happening.)

I like our way much better -- once you've learned a bit about the system
tables, you can use them in any query, etc. However, many new users do try
DESCRIBE, etc.

Would it be worthwhile for psql / PG parser to notice these attempts and
bleat out some help? It doesn't quite seem worthwhile to me, but perhaps
others think it is.

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Quoting Joel Burton (jburton@scw.org):

I like our way much better -- once you've learned a bit about the system
tables, you can use them in any query, etc. However, many new users do try
DESCRIBE, etc.

Well, I worked for Oracle for 6 months and got quite adept at querying the
system tables. But that was a while ago, and I forget it all.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"low ping bastard: n. anybody getting more frags than the person running their
client on the server." - Steve Caskey