0

I'm trying to generate a materialized view, where I want to calculate the mean value of a specific interval in a column for several columns.

CREATE TABLE decade_2000_cg_data_view AS 
 SELECT tdl.id, tdl.grid_id, tdl.name, depth1.cg, depth2.cg, depth3.cg, ..., t.tair 
 FROM temperature_depth_level tdl  
 LEFT JOIN LATERAL(select (select avg(val) from unnest(depth_level1[start:end]) as val) as cg 
      FROM temperature_depth_level) AS depth1 
 LEFT JOIN LATERAL(select (select avg(val) from unnest(depth_level2[start:end]) as val) as cg 
      FROM temperature_depth_level) AS depth2 
 LEFT JOIN LATERAL(select (select avg(val) from unnest(depth_level3[start:end]) as val) as cg 
      FROM temperature_depth_level) AS depth3  
 ...
 LEFT JOIN LATERAL(select (select avg(val) from unnest(tair[start:end]) as val) as tair 
      FROM temperature_depth_level) AS t

I run the query in my console and get this error message:

execute_sql() error: ERROR:  syntax error at the end of input

I'm running on postgresql version 13.3

The idea was to create a materialized view, so the request on the data from my website is faster and not to calculate it in the backend on request. This I have roughly 800 datapoints I have to calculate over a very big interval. This query is generated through a python script. Maybe there is a better way, but I don't know. Maybe somebody has an idea.

Lila
  • 1
  • 1
  • You are missing the join conditions (`ON ...`) for all your joins –  Dec 10 '21 at 12:18
  • Probably not related to your error bu to create a materialized view you must use 'CREATE MATERIALIZED VIEW', not 'CREATE TABLE'. – StephaneM Dec 10 '21 at 12:59
  • @StephaneM oh yes, that's true. I changed so much and tried so many things, that I didn't change that back. – Lila Dec 10 '21 at 13:22
  • @a_horse_with_no_name eventhough they're from the same table? I would just check if the id is the same than. – Lila Dec 10 '21 at 13:25
  • It doesn't matter _what_ you join. Every join except `cross join` requires a join condition –  Dec 10 '21 at 13:26

0 Answers0