| |||||||||||||
quite nicequite nicePosted Jul 1, 2009 16:51 UTC (Wed) by ccyoung (guest, #16340)Parent article: PostgreSQL 8.4 released
needed doc:
window: http://www.postgresql.org/docs/8.4/static/tutorial-window... recursive: http://www.postgresql.org/docs/8.4/static/queries-with.html
recursive approach was complete surprise (to me) without "connect by" syntax. no coffee yet, so don't understand the overall coolness of WITH.
suppress_redundent_updates() sounds like a godsend, but can find no doc on how to actually use it
array_agg() and unnest() are cute little guys that I think will see a lot of use (remember that arrays can be used for "in" in selects)
citext data type for case insensitive text - wonder if it automatically indexes on one case to preserve speed.
LIMIT (expression or subquery) - now if ALTER SEQUENCE could do the same
going up today. congrats to the pg team!!!
(Log in to post comments)
quite nice Posted Jul 1, 2009 18:28 UTC (Wed) by tbrownaw (guest, #45457) [Link]
"connect by" is Oracle-specific, I believe the "recursive WITH" is the ANSI SQL standard way to do recursive queries (but I haven't seen the RECURSIVE keyword mentioned anywhere before, so no idea if this is exactly how the standard says). WITH in general is just a useful way to factor out complicated subqueries, sometimes it's easier to read top-to-bottom instead of inside-to-outside and sometimes you need the same subquery several times.
quite nice Posted Jul 1, 2009 21:37 UTC (Wed) by flewellyn (subscriber, #5047) [Link]
Wow, those window functions and WITH queries would be amazingly powerful with PostGIS. I'm going to have to schedule an upgrade within the next few months!
ALTER SEQUENCE (expression) Posted Jul 1, 2009 22:03 UTC (Wed) by alvherre (subscriber, #18730) [Link]
Don't hold your breath for using an expression in ALTER SEQUENCE, but you can already use one with the setval() function.
The commenter above is right that WITH RECURSIVE is part of the SQL standard. Yes, RECURSIVE too.
|
|||||||||||||