[SQL] Hey! ORDER BY in VIEWS?
Tom, Stephan, Hey! I thought you couldn't do ORDER BY in views ... yet I just did. Is this a new thing, or am I just getting my Trasact-SQL and my PostgreSQL mixed up again? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: [SQL] Hey! ORDER BY in VIEWS?
I think PostgreSQL allows you to do an ORDER BY in a view, but the real message is that it just doesn't make any sense. Remember that a view is just a "virtual table", not a query. If you "order by" as part of it's definition, there's no guarantee that the data will be orded when you SELECT FROM later on. Always, always, always include an ORDER BY clause in every select you do. (I personally think SQL ought to REQUIRE it!) -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus Sent: Sunday, July 15, 2001 12:22 PM To: [EMAIL PROTECTED] Subject: [SQL] Hey! ORDER BY in VIEWS? Tom, Stephan, Hey! I thought you couldn't do ORDER BY in views ... yet I just did. Is this a new thing, or am I just getting my Trasact-SQL and my PostgreSQL mixed up again? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Hey! ORDER BY in VIEWS?
Josh Berkus writes: > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? I think it was allowed from 7.1 on to enable LIMIT in views to work sensibly. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Hey! ORDER BY in VIEWS?
I think Tom fixed that in 7.1.X. That is why it now works. > Tom, Stephan, > > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? > > -Josh > > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Hey! ORDER BY in VIEWS?
Pater, Robbie, Bruce, > > Hey! I thought you couldn't do ORDER BY in views ... yet I just > did. > > Is this a new thing, or am I just getting my Trasact-SQL and my > > PostgreSQL mixed up again? > > I think it was allowed from 7.1 on to enable LIMIT in views to work > sensibly. Makes sense. I take it that this is a deviation from the ANSI 92 standard, then? What happens if I put an ORDER BY in a view, then call an ORDER BY in a query, e.g.: CREATE VIEW test_view AS SELECT client_name, city, zip FROM clients WHERE zip IS NOT NULL ORDER BY zip; SELECT * FROM test_view ORDER BY city; Does the second ORDER BY override or suppliment the view ORDER BY, or is it ignored? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: [SQL] Hey! ORDER BY in VIEWS?
Josh: You wondered: >What happens if I put an ORDER BY in a view, then call an ORDER BY in a >query, e.g.: > >CREATE VIEW test_view AS >SELECT client_name, city, zip FROM clients >WHERE zip IS NOT NULL >ORDER BY zip; > >SELECT * FROM test_view ORDER BY city; > >Does the second ORDER BY override or suppliment the view ORDER BY, or is >it ignored? I think this question falls into the "Don't ask, don't tell" category of computer related questions. I can't think of a reason to design this way, the behavior isn't specified or sensible, so just don't do it! Sorry if I'm over-admonishing. Curiosity killed the cat. -Robby ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Hey! ORDER BY in VIEWS?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Josh Berkus writes: >> Hey! I thought you couldn't do ORDER BY in views ... yet I just did. >> Is this a new thing, or am I just getting my Trasact-SQL and my >> PostgreSQL mixed up again? > I think it was allowed from 7.1 on to enable LIMIT in views to work > sensibly. The point being that ORDER BY + LIMIT is actually a useful computational extension. ORDER BY per se, in a view, is rather pointless since any query that selects from the view will feel free to rearrange the data for its convenience. Hmm, I just realized that there's a bug here: let's say you have CREATE VIEW latest AS SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1; ie, this view gives you the latest news story. If you do SELECT * FROM latest WHERE story_text LIKE '%Joe Smith%'; what you will get in 7.1 is the latest story mentioning Joe Smith, because the planner will push down the WHERE clause into the view's SELECT, where it'll be applied before the LIMIT. Perhaps some would call this useful behavior, but I'd say it has to be considered a bug :-(. The outer WHERE should not cause the VIEW to return a different row than it otherwise would. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Hey! ORDER BY in VIEWS?
Tom, > Hmm, I just realized that there's a bug here: let's say you have > > CREATE VIEW latest AS > SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1; > > ie, this view gives you the latest news story. If you do > > SELECT * FROM latest WHERE story_text LIKE '%Joe Smith%'; > > what you will get in 7.1 is the latest story mentioning Joe Smith, > because the planner will push down the WHERE clause into the view's > SELECT, where it'll be applied before the LIMIT. Perhaps some would > call this useful behavior, but I'd say it has to be considered a bug > :-(. > The outer WHERE should not cause the VIEW to return a different row > than it otherwise would. Now you see why SQL92 doesn't support ORDER BY in views. ;-) Why, exactly, do we need to support ORDER BY ... LIMIT in VIEWS, anyway? I'm frankly unclear on the utility of this ... I make SQL jump through some pretty fancy hoops, myself (4 section UNION query with nested subselects, anyone?) and I've never needed ... or wanted ... a view with a built-in LIMIT. If we gotta have 'em, though, Tom, you'd have to code in an exception to the VIEW optimizer that doesn't push down WHERE clauses if the VIEW has an ORDER BY ... LIMIT statement. Sure you wanna get into this? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Hey! ORDER BY in VIEWS?
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> Hmm, I just realized that there's a bug here: let's say you have >> >> CREATE VIEW latest AS >> SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1; >> >> ie, this view gives you the latest news story. > Why, exactly, do we need to support ORDER BY ... LIMIT in VIEWS, anyway? > I'm frankly unclear on the utility of this ... I think the above example is pretty compelling, don't you? Easy to read and it generates a very nice indexscan plan. > If we gotta have 'em, though, Tom, you'd have to code in an exception to > the VIEW optimizer that doesn't push down WHERE clauses if the VIEW has > an ORDER BY ... LIMIT statement. Sure you wanna get into this? It's a one-line addition to code that already knows that certain kinds of clauses (like UNION) prevent pushdown. Just an oversight, not a fundamental flaw. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Unknown values in int8 fields?
Hi Chris,
When I load records with copy from ... And one of the fields (last
one) is left empty, I want the default of nextval('sequence_table') to kick
in - but it doesn't.
Now, the field with a unique index on it stays blank. No big deal if I could
go and say 'update rate set recno = nextval('sequence_rate') where recno = 0
- but it's not that easy as the recno is not 0 but - hmm what? What can I
check for? I tried '?' and ? And 0 and ... And ... And ... But nothing works
... Now what?
Best regards
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: [SQL] Unknown values in int8 fields?
I've not been following this too closely but it sounds like you are
trying to COPY records from table A to table B, where the table B
also includes a serial value.
Here's an easy trick which I'm pretty sure will work: instead of
using COPY use SELECT INTO. It's much slower but I think it will
do the trick.
Good luck! HTH.
-Robby
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Chris Ruprecht
Sent: Sunday, July 15, 2001 9:06 PM
To: [EMAIL PROTECTED]
Subject: [SQL] Unknown values in int8 fields?
Hi Chris,
When I load records with copy from ... And one of the fields (last
one) is left empty, I want the default of nextval('sequence_table') to kick
in - but it doesn't.
Now, the field with a unique index on it stays blank. No big deal if I could
go and say 'update rate set recno = nextval('sequence_rate') where recno = 0
- but it's not that easy as the recno is not 0 but - hmm what? What can I
check for? I tried '?' and ? And 0 and ... And ... And ... But nothing works
... Now what?
Best regards
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] ANNOUNCE: Updated PostgreSQL Notes
Finally got my act together and updated my PostgreSQL Notes. You can access them from http://techdocs.postgresql.org (another plug for Justin ;-) Changes include: - downloadable zipped version - short example of accessing PostgreSQL from PHP - short example of accessing PostgreSQL from Perl (Pg & DBI) - typos corrected etc. If you do download it, please take a minute to send some feedback, coz I can't tell which pages are useful without it. - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Unknown values in int8 fields?
Have you tried "where recno IS NULL"?
- Original Message -
From: "Chris Ruprecht" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, July 15, 2001 7:05 PM
Subject: [SQL] Unknown values in int8 fields?
> Hi Chris,
>
> When I load records with copy from ... And one of the fields (last
> one) is left empty, I want the default of nextval('sequence_table') to
kick
> in - but it doesn't.
> Now, the field with a unique index on it stays blank. No big deal if I
could
> go and say 'update rate set recno = nextval('sequence_rate') where recno =
0
> - but it's not that easy as the recno is not 0 but - hmm what? What can I
> check for? I tried '?' and ? And 0 and ... And ... And ... But nothing
works
> ... Now what?
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
