pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgenerate_series to return row that doesn't exist in table...
3+ messages / 2 participants
[nested] [flat]
* generate_series to return row that doesn't exist in table...
@ 2006-03-24 20:30 MaXX <bs139412@skynet.be>
2006-03-24 20:42 ` Re: generate_series to return row that doesn't exist in Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 3+ messages in thread
From: MaXX @ 2006-03-24 20:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table wich contains aggregated data,
table stats_activity
logtime timestamptz,
count int
given this dataset
"2006-03-24 03:00:00+01";55
"2006-03-24 04:00:00+01";33
"2006-03-24 06:00:00+01";46
"2006-03-24 07:00:00+01";63
"2006-03-24 08:00:00+01";88
I want to get this in order to plot the data
"2006-03-24 03:00:00+01";55
"2006-03-24 04:00:00+01";33
>>"2006-03-24 05:00:00+01";0<<
"2006-03-24 06:00:00+01";46
"2006-03-24 07:00:00+01";63
"2006-03-24 08:00:00+01";88
I used generate_series to get all the timestamps I need but I don't know how
to write my query.
I've tried various combination of subselects, joins, union,... and I never
managed to get the result I wanted... I'm sure the solution is trivial but I
don't get it...
I prefer to generate missing rows "on the fly" intead of actually storing
useless data on the table.
Thanks for your help,
--
MaXX
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: generate_series to return row that doesn't exist in
2006-03-24 20:30 generate_series to return row that doesn't exist in table... MaXX <bs139412@skynet.be>
@ 2006-03-24 20:42 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-24 20:54 ` Re: generate_series to return row that doesn't exist in MaXX <bs139412@skynet.be>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2006-03-24 20:42 UTC (permalink / raw)
To: MaXX <bs139412@skynet.be>; +Cc: pgsql-sql
On Fri, 2006-03-24 at 14:30, MaXX wrote:
> Hi,
>
> I have a table wich contains aggregated data,
> table stats_activity
> logtime timestamptz,
> count int
>
> given this dataset
> "2006-03-24 03:00:00+01";55
> "2006-03-24 04:00:00+01";33
> "2006-03-24 06:00:00+01";46
> "2006-03-24 07:00:00+01";63
> "2006-03-24 08:00:00+01";88
>
> I want to get this in order to plot the data
> "2006-03-24 03:00:00+01";55
> "2006-03-24 04:00:00+01";33
> >>"2006-03-24 05:00:00+01";0<<
> "2006-03-24 06:00:00+01";46
> "2006-03-24 07:00:00+01";63
> "2006-03-24 08:00:00+01";88
>
> I used generate_series to get all the timestamps I need but I don't know how
> to write my query.
>
>
> I've tried various combination of subselects, joins, union,... and I never
> managed to get the result I wanted... I'm sure the solution is trivial but I
> don't get it...
> I prefer to generate missing rows "on the fly" intead of actually storing
> useless data on the table.
More than likely you need a left join and a case statement.
select <selectlist>, case when a.date is null then 0 else a.date end
from (select * from generate_series() -- magic to get dates goes here)
as p left join maintable as a on (p.date=a.date);
There may be some small syntax error in there, as I've not tested it.
The relavent pages are:
case:
http://www.postgresql.org/docs/8.1/static/functions-conditional.html
joins:
http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: generate_series to return row that doesn't exist in
2006-03-24 20:30 generate_series to return row that doesn't exist in table... MaXX <bs139412@skynet.be>
2006-03-24 20:42 ` Re: generate_series to return row that doesn't exist in Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-03-24 20:54 ` MaXX <bs139412@skynet.be>
0 siblings, 0 replies; 3+ messages in thread
From: MaXX @ 2006-03-24 20:54 UTC (permalink / raw)
To: pgsql-sql
On Friday 24 March 2006 21:42, Scott Marlowe wrote:
> More than likely you need a left join and a case statement.
>
> select <selectlist>, case when a.date is null then 0 else a.date end
> from (select * from generate_series() -- magic to get dates goes here)
> as p left join maintable as a on (p.date=a.date);
>
> There may be some small syntax error in there, as I've not tested it.
> The relavent pages are:
>
> case:
> http://www.postgresql.org/docs/8.1/static/functions-conditional.html
>
> joins:
> http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html
Perfect!!
Time to RTFM again...
Thanks,
--
MaXX
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-24 20:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 20:30 generate_series to return row that doesn't exist in table... MaXX <bs139412@skynet.be>
2006-03-24 20:42 ` Re: generate_series to return row that doesn't exist in Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-24 20:54 ` Re: generate_series to return row that doesn't exist in MaXX <bs139412@skynet.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox