Thread: BETWEEN clause
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? -- Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody There is no substitute for good manners, except, perhaps, fast reflexes.
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