How do I create named window partitions (aliases) in PostgreSQL?

Question

The documentation for PostgreSQL window functions seems to imply you can use the same named window in multiple places in your query. However, I can't figure out how do I create a named window?

SELECT first_value(vin) OVER( PARTITION BY vin ) AS w, first_value(make) OVER w
FROM inventory.vehicles
WHERE lot_Id = 9999 AND make is not null;

This is a joke query I'm trying to get the syntax to take, but I'm getting error:

ERROR: window "w" does not exist

Evan Carroll · Accepted Answer · 2018-04-28 16:28:18Z

19

The answer was actually in the SELECT doc:

WINDOW Clause

The optional WINDOW clause has the general form

WINDOW window_name AS ( window_definition ) [, ...]

Here is an example,

SELECT first_value(vin) OVER w,
  first_value(make) OVER w
FROM inventory.vehicles
WHERE lot_Id = 9999
  AND make is not null
WINDOW w AS ( PARTITION by vin );

edited Apr 28, 2018 at 16:28

answered Aug 10, 2010 at 23:14

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I create named window partitions (aliases) in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related