Postgresql – group several columns together in materialized view

materialized-viewpostgispostgresqlpostgresql-9.6

For statistical reasons, I need to group several ids in a single column of a materialized view.
This should form a list of ids from other tables with an info. Further processing will be done on this view. The goal is to save seconds of processing by first generating this view instead of querying all the tables (about 10) one by one.

But I don't understand if I can create a new column name with view or only select one in existing table.

enter image description here

Best Answer

There are two ways to specify the name of a view column:

  1. Do it with a column list:

    CREATE VIEW myview (colname1, colname2) AS
SELECT appa.appa_id,
       font.font_id
FROM appa JOIN font ON ...;

  2. Do it with aliases in the defining query:

    CREATE VIEW myview AS
SELECT appa.appa_id AS colname1,
       font.font_id AS colname2
FROM appa JOIN font ON ...;
Related Question