Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    20

    Red face Unanswered: error near CREATE MATERIALIZED VIEW

    I just created a materialized view on my test table successfully and now trying to create on a server table. but I am getting error near MATERIALIZED VIEW. How can fix this issue. My query is as below:
    CREATE MATERIALIZED VIEW sensor_data_mat AS SELECT t
    , MAX( CASE sensorid WHEN 16 THEN unscaled_val END ) AS unscaled_val_16
    , MAX( CASE sensorid WHEN 16 THEN scaled_val END ) AS scaled_val_16
    , MAX( CASE sensorid WHEN 17 THEN unscaled_val END ) AS unscaled_val_17
    , MAX( CASE sensorid WHEN 17 THEN scaled_val END ) AS scaled_val_17
    , MAX( CASE sensorid WHEN 18 THEN unscaled_val END ) AS unscaled_val_18
    , MAX( CASE sensorid WHEN 18 THEN scaled_val END ) AS scaled_val_18
    , MAX( CASE sensorid WHEN 19 THEN unscaled_val END ) AS unscaled_val_19
    , MAX( CASE sensorid WHEN 19 THEN scaled_val END ) AS scaled_val_19
    , MAX( CASE sensorid WHEN 20 THEN unscaled_val END ) AS unscaled_val_20
    , MAX( CASE sensorid WHEN 20 THEN scaled_val END ) AS scaled_val_20
    , MAX( CASE sensorid WHEN 21 THEN unscaled_val END ) AS unscaled_val_21
    , MAX( CASE sensorid WHEN 21 THEN scaled_val END ) AS scaled_val_21
    , MAX( CASE sensorid WHEN 22 THEN unscaled_val END ) AS unscaled_val_22
    , MAX( CASE sensorid WHEN 22 THEN scaled_val END ) AS scaled_val_22
    , MAX( CASE sensorid WHEN 23 THEN unscaled_val END ) AS unscaled_val_23
    , MAX( CASE sensorid WHEN 23 THEN scaled_val END ) AS scaled_val_23
    , MAX( CASE sensorid WHEN 24 THEN unscaled_val END ) AS unscaled_val_24
    , MAX( CASE sensorid WHEN 24 THEN scaled_val END ) AS scaled_val_24
    , MAX( CASE sensorid WHEN 25 THEN unscaled_val END ) AS unscaled_val_25
    , MAX( CASE sensorid WHEN 25 THEN scaled_val END ) AS scaled_val_25
    , MAX( CASE sensorid WHEN 26 THEN unscaled_val END ) AS unscaled_val_26
    , MAX( CASE sensorid WHEN 26 THEN scaled_val END ) AS scaled_val_26
    , MAX( CASE sensorid WHEN 27 THEN unscaled_val END ) AS unscaled_val_27
    , MAX( CASE sensorid WHEN 27 THEN scaled_val END ) AS scaled_val_27
    , MAX( CASE sensorid WHEN 28 THEN unscaled_val END ) AS unscaled_val_28
    , MAX( CASE sensorid WHEN 28 THEN scaled_val END ) AS scaled_val_28
    , MAX( CASE sensorid WHEN 29 THEN unscaled_val END ) AS unscaled_val_29
    , MAX( CASE sensorid WHEN 29 THEN scaled_val END ) AS scaled_val_29
    , MAX( CASE sensorid WHEN 30 THEN unscaled_val END ) AS unscaled_val_30
    , MAX( CASE sensorid WHEN 30 THEN scaled_val END ) AS scaled_val_30
    , MAX( CASE sensorid WHEN 31 THEN unscaled_val END ) AS unscaled_val_31
    , MAX( CASE sensorid WHEN 31 THEN scaled_val END ) AS scaled_val_31
    , MAX( CASE sensorid WHEN 32 THEN unscaled_val END ) AS unscaled_val_32
    , MAX( CASE sensorid WHEN 32 THEN scaled_val END ) AS scaled_val_32
    , MAX( CASE sensorid WHEN 33 THEN unscaled_val END ) AS unscaled_val_33
    , MAX( CASE sensorid WHEN 33 THEN scaled_val END ) AS scaled_val_33
    , MAX( CASE sensorid WHEN 34 THEN unscaled_val END ) AS unscaled_val_34
    , MAX( CASE sensorid WHEN 34 THEN scaled_val END ) AS scaled_val_34
    , MAX( CASE sensorid WHEN 35 THEN unscaled_val END ) AS unscaled_val_35
    , MAX( CASE sensorid WHEN 35 THEN scaled_val END ) AS scaled_val_35
    , MAX( CASE sensorid WHEN 36 THEN unscaled_val END ) AS unscaled_val_36
    , MAX( CASE sensorid WHEN 36 THEN scaled_val END ) AS scaled_val_36
    , MAX( CASE sensorid WHEN 37 THEN unscaled_val END ) AS unscaled_val_37
    , MAX( CASE sensorid WHEN 37 THEN scaled_val END ) AS scaled_val_37
    , MAX( CASE sensorid WHEN 38 THEN unscaled_val END ) AS unscaled_val_38
    , MAX( CASE sensorid WHEN 38 THEN scaled_val END ) AS scaled_val_38
    , MAX( CASE sensorid WHEN 39 THEN unscaled_val END ) AS unscaled_val_39
    , MAX( CASE sensorid WHEN 39 THEN scaled_val END ) AS scaled_val_39
    , MAX( CASE sensorid WHEN 40 THEN unscaled_val END ) AS unscaled_val_40
    , MAX( CASE sensorid WHEN 40 THEN scaled_val END ) AS scaled_val_40
    , MAX( CASE sensorid WHEN 41 THEN unscaled_val END ) AS unscaled_val_41
    , MAX( CASE sensorid WHEN 41 THEN scaled_val END ) AS scaled_val_41
    , MAX( CASE sensorid WHEN 42 THEN unscaled_val END ) AS unscaled_val_42
    , MAX( CASE sensorid WHEN 42 THEN scaled_val END ) AS scaled_val_42
    , MAX( CASE sensorid WHEN 43 THEN unscaled_val END ) AS unscaled_val_43
    , MAX( CASE sensorid WHEN 43 THEN scaled_val END ) AS scaled_val_43
    , MAX( CASE sensorid WHEN 44 THEN unscaled_val END ) AS unscaled_val_44
    , MAX( CASE sensorid WHEN 44 THEN scaled_val END ) AS scaled_val_44
    , MAX( CASE sensorid WHEN 45 THEN unscaled_val END ) AS unscaled_val_45
    , MAX( CASE sensorid WHEN 45 THEN scaled_val END ) AS scaled_val_45
    , MAX( CASE sensorid WHEN 46 THEN unscaled_val END ) AS unscaled_val_46
    , MAX( CASE sensorid WHEN 46 THEN scaled_val END ) AS scaled_val_46
    , MAX( CASE sensorid WHEN 63 THEN unscaled_val END ) AS unscaled_val_63
    , MAX( CASE sensorid WHEN 63 THEN scaled_val END ) AS scaled_val_63
    , MAX( CASE sensorid WHEN 64 THEN unscaled_val END ) AS unscaled_val_64
    , MAX( CASE sensorid WHEN 64 THEN scaled_val END ) AS scaled_val_64
    , MAX( CASE sensorid WHEN 65 THEN unscaled_val END ) AS unscaled_val_65
    , MAX( CASE sensorid WHEN 65 THEN scaled_val END ) AS scaled_val_65
    , MAX( CASE sensorid WHEN 66 THEN unscaled_val END ) AS unscaled_val_66
    , MAX( CASE sensorid WHEN 66 THEN scaled_val END ) AS scaled_val_66
    FROM sensor_data_backup GROUP BY t;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please post the exact error message.

    And post the result of
    Code:
    select version();
    And the next time please format your SQL code properly
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    May 2014
    Posts
    20

    Error was the psql version

    Thanks, the error was that 8.4 do not support the materialized views.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •