xtest=> show table tableC;
COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT
-------------+---------------+-----------+-------------+-----+---------
id | 4 | INTEGER | YES | NO |
stime | 93 | TIMESTAMP | YES | NO |
val | -5 | BIGINT | YES | NO |
(3 rows)
xtest=> select * from tableC;
id | stime | val
----+-------------------------+-------
1 | 2013-10-16 16:56:20.953 | 11111
(1 row)
xtest=> select * from (select stime, val,val2,''::text as t1, ''::text as t2 from tableB
union
select stime, val,0::bigint as val2,''::text as t1, ''::text as t2 from tableC) t order by stime;
stime | val | val2 | t1 | t2
-------------------------+-------+------------+----+----
2013-10-16 16:09:05.078 | 67890 | 1234567890 | |
2013-10-16 16:56:20.953 | 11111 | 0 | |
(2 rows)
Below SQL is OK:
xtest=> show table tableC; ------+ ------- ------- -+----- ------+ ------- ------+ -----+- ------- -
COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT
-------
id | 4 | INTEGER | YES | NO |
stime | 93 | TIMESTAMP | YES | NO |
val | -5 | BIGINT | YES | NO |
(3 rows)
xtest=> select * from tableC; ------- ------- ------- --+---- --- ------- ------- ----+-- -----+- ------- ----+-- --+----
id | stime | val
----+--
1 | 2013-10-16 16:56:20.953 | 11111
(1 row)
xtest=> select * from (select stime, val,val2,''::text as t1, ''::text as t2 from tableB
union
select stime, val,0::bigint as val2,''::text as t1, ''::text as t2 from tableC) t order by stime;
stime | val | val2 | t1 | t2
-------
2013-10-16 16:09:05.078 | 67890 | 1234567890 | |
2013-10-16 16:56:20.953 | 11111 | 0 | |
(2 rows)