Combine two tables into one materialized view

Question

i am trying to combine two tables (POIs) into one materialized view but they don't have the same columns.

For example:

Table a: attributes: name, type, geoloc, symbolnumber

Table b: attributes: name, type, info1, info2, geoloc

I tried "JOIN" and "UNION ALL" between the SELECT statements but it won't work. Any suggestions? Perfect solution would be to fill the empty colums with NULLs if for example Table a has no "info1".

Here is the original code:

DROP MATERIALIZED VIEW vt_views.poitest;

CREATE MATERIALIZED VIEW vt_views.poitest
TABLESPACE pg_default
AS
 SELECT tim_bergname.name,
 tim_bergname.info1 AS hoehe,
 tim_bergname.info2 AS region,
 tim_bergname.geoloc,
 tim_bergname.symbolnummer
 FROM tim_bergname

JOIN

SELECT tim_flurname.name,
 tim_flurname.geoloc,
 tim_flurname.symbolnummer
 FROM tim_flurname


WITH DATA;

ALTER TABLE vt_views.poitest
 OWNER TO postgres;

GRANT ALL ON TABLE vt_views.poi TO postgres;
GRANT ALL ON TABLE vt_views.poi TO PUBLIC;

Thanks

score 3 · Accepted Answer · answered May 12 '20 at 17:18

You need UNION ALL, and both subselects must have the same number and type of columns:

CREATE MATERIALIZED VIEW vt_views.poitest AS
   SELECT tim_bergname.name,
          tim_bergname.info1 AS hoehe,
          tim_bergname.info2 AS region,
          tim_bergname.geoloc,
          tim_bergname.symbolnummer
   FROM tim_bergname
UNION ALL
   SELECT tim_flurname.name,
          NULL,
          NULL,
          tim_flurname.geoloc,
          tim_flurname.symbolnummer
   FROM tim_flurname;

I posted an answer to my question below because stackoverflow would not let me comment the huge code. It is not working right now. — thecartographer, May 13 '20 at 10:04

Combine two tables into one materialized view

1 Answers1