Question 88961 in PostgreSQL Question posted in
The official documentation can be found here.

PostgeSQL: Window function behaves different in view – Postgresql

Example table with one column tmp_year_test.year and values:

year
2010
2011
2012

And now the logic

-- 2010 - OK
select
    max(year) over () as max_year
from tmp_year_test
where year = 2010

-- let's put it in the view
create view tmp_max_year_test as
select  
    year, 
    max(year) over () as max_year
from tmp_year_test

-- 2012 - WHY?
select  
    max_year
from tmp_max_year_test
where year = 2010

I thought I am fetching the same column as I do without the view, but the value is different. Why?
I need to store the wind. function logic in the view and get it filtered. Is there a way to achieve it?

EDIT:
A better example of the desired behavior:

Example 1

select  
    *
from tmp_max_year_test
where year in (2011,2012)
year max_year
2011 2012
2012 2012

Example 2

select  
    *
from tmp_max_year_test
where year in (2010,2011)
year max_year
2010 2011
2011 2011
Tags:

2

Answers


  1. 0

    When you create the view, there is no partition by clause in the window function, so the aggregate function is applied on the entire table. Since 2012 is the greatest values of all in the source table, the view contains the various years (year) and the constant value 2012 as max_year.

    The doc has a very similar example and adds

    SELECT salary, sum(salary) OVER () FROM empsalary;
    Above, since there is no ORDER BY in the OVER clause, the window frame
    is the same as the partition, which for lack of PARTITION BY is the
    whole table; in other words each sum is taken over the whole table and
    so we get the same result for each output row.

    In the 1st query, you are restricting the max computation to a single row, the one for the year 2010 so the max_year is also 2010.

    To achieve a similar result when using the view, you need to specify a partition in the over clause:

    create or replace view tmp_max_year_test as
select  
    year, 
    max(year) over (PARTITION BY year) as max_year
from tmp_year_test;
    Login or Signup to reply.
  2. 0

    Your view has these 3 records:

    select  
    year, 
    max(year) over () as max_year
from tmp_year_test

    records:

    year max_year
    2010 2012
    2011 2012
    2012 2012

    So, when asking :

    select  
    max_year
from tmp_max_year_test
where year = 2010

    It seems pretty logical that 2012 is returned…

    see: DBFIDDLE for the complete SQL stuff.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search