Skip site navigation (1) Skip section navigation (2)

Re: Joining time fields?

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: oliveiros(dot)cristina(at)asperger-talents(dot)com
Subject: Re: Joining time fields?
Date: 2012-07-26 20:06:50
Message-ID: 5011A35A.7070605@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 07/26/2012 12:14 PM, Oliveiros d'Azevedo Cristina wrote:
> Howdy, Bryan,
> I am not familiar with many constructs you employed, like crossed, 
> grouped and using.
> What is the version of PostGreSQL you are using? I'm still using 8.3
> If your query works and your print out does prove so, then you found 
> James an elegant solution.
> I'm an old dog but I must find the time to upgrade myself, I'm missing 
> lots of constructs. I'm always trying to do queries with the concepts 
> I know and I'm
> aware that probably new versions bring new constructs that can make in 
> one line what I do in 10 and in a much more efficient way.

The construct you want to research is the common-table-expression (CTE).

A common table expression acts sort of like a temporary table or view 
that is created just for the duration of that query. The "with crossed 
as (...)" and "with grouped as (...)" make temporary "tables" (table 
expressions) called "crossed" and "grouped" that are referenced in the 
final part of the query as though they were tables. "Crossed" and 
"grouped" are just names assigned to the CTEs, not new constructs.

An exciting capability provided through the use of CTEs is the ability 
to write recursive queries which greatly simplifies dealing with 
hierarchical data like org-charts.

Common table expressions 
(http://www.postgresql.org/docs/current/static/queries-with.html) along 
with windowing functions 
(http://www.postgresql.org/docs/current/static/tutorial-window.html) are 
two very useful features that were introduced in version 8.4 
(http://www.postgresql.org/docs/8.4/static/release-8-4.html).

Go forth and upgrade! (8.3 is EOL in a few months, anyway.)

Cheers,
Steve

In response to

pgsql-novice by date

Next:From: Jonatan ReinersDate: 2012-07-27 09:35:35
Subject: problem with pg_dump
Previous:From: Bryan Lee NuseDate: 2012-07-26 19:35:17
Subject: Re: Joining time fields?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group