Nevermind, I misunderstood your question.
The answer is an outer join and if you want the exact output you provided then you can use the following clause.
coalesce(dx, dx1) as date
Is there any reason why these are two different tables? I'd consider changing data structure.
----- Original Message -----
From: "Tim Smith" <randomdev4+postgres@gmail.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, July 14, 2016 2:56:19 PM
Subject: [GENERAL] Merging timeseries in postgres
Hi,
I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.
Assume I have a bunch of time-series tables :
create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
insert into test1 values('2002-01-02','0.002');
insert into test1 values('2003-01-03','0.002');
What I want to do is create a view that merges these together with
time as the index, i.e the output would look like :
2000-01-01 0.001 (null)
2002-01-02 (null) 0.002
2003-01-03 (null) 0.003
I can't quite figure out how to keep the index independent and make a
clean join, typical outer join constructs end up with results like :
dx | nx | dx1 | nx1
----+----+------------+-------
| | 2002-01-02 | 0.001
| | 2003-01-02 | 0.002
(2 rows)
dx | nx | dx1 | nx1
------------+-------+------------+-------
2000-01-02 | 0.005 | |
| | 2002-01-02 | 0.001
| | 2003-01-02 | 0.002
Which isn't very pretty and doesn't really achieve what I want.
As I said "sql-writers block !" ;-(
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general