I have complex view with calculations but one line makes issue

ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric)

When I execute the query, calculation works fine, when I use it in view, all works well, but if I try to create materialized view with this statement, I get error

" a negative number raised to a non-integer power yields a complex result"

is there any way how can I use this in materialized view? or is there some explanation why this does not work for materialized view only?

  • 1
    Check the data in latest_revenue and oper_reve_3y_old . I think you have some data that produces negative number based on your calculations. Check this link if it helps: stackoverflow.com/questions/50653736/… Sep 7 at 12:24
  • you're right, problem is with oper_reve_3y_old which contains negative number , but now I just dont understand why I can create normal view, but not materialized
    – Mi Ro
    Sep 7 at 15:00

You will get the same error if you query the whole view:

SELECT * FROM view_name;

You probably don't see the error when you use the view because you are using additional WHERE conditions that avoid the problematic values.

Remember that a view is just a named SELECT statement, so querying a view is not necessarily done by first calculating the whole view and then applying conditions. However, when you create a materialized view, all data from the view have to be selected.

  • Thanks, actually select * from view works without where same as select * from query...the reason is that dbeaver I use is querying by parts and those I see in result are not problematic. But can you explain me this behavior of postgres please?
    – Mi Ro
    Sep 8 at 17:48
  • I thought I had explained the behavior. Your client doesn't add a WHERE condition, but a LIMIT clause, which has the same effect: not all rows are calculated. Sep 9 at 2:24
  • yes I understand that, but I dont understand why postgres cannot calculate this if calculator is able to do that...I am trying to find out root cause of this issue in postgres
    – Mi Ro
    Sep 13 at 9:22
  • 1
    Then your calculator can handle complex numbers. numeric or double precision cannot do that. Sure, you could define your own complex data type (or choose an existing extension) that implements such an operation. Sep 13 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.