Window Functions in PostgreSQL vs Oracle: Same Power, Better SQL

The Good News: Window Functions Port Almost Verbatim

Of all the things that make an Oracle→PostgreSQL migration painful — packages, sequences, CONNECT BY, LOBs — window functions are refreshingly not one of them. The analytic syntax you already know from Oracle is the ANSI SQL standard, and PostgreSQL implements it faithfully. ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, running totals with OVER (PARTITION BY … ORDER BY …) — all present, same names, same semantics.

-- This query is byte-for-byte identical in Oracle and PostgreSQL
SELECT
  employee_id,
  department_id,
  salary,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
  LAG(salary)  OVER (PARTITION BY department_id ORDER BY hire_date)   AS prev_hire_salary,
  SUM(salary)  OVER (PARTITION BY department_id ORDER BY hire_date
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

If your reports lean on analytics, that’s most of your window-function surface migrating with zero edits. The value of this post is the remaining 10% — three differences that pass a syntax check and then return the wrong answer, or don’t exist at all.

The LAST_VALUE Frame Trap (Both Databases)

This one is not an Oracle-vs-PostgreSQL difference — it’s a trap they share, which is exactly why it survives migration unnoticed. When you add ORDER BY to a window without an explicit frame, the default frame in both databases is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So FIRST_VALUE works as you’d expect, but LAST_VALUE returns the current row’s value — because “the last row” in a frame that ends at the current row is the current row.

-- WRONG: last_salary just echoes the current row's salary
SELECT employee_id,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS last_salary
FROM employees;

-- RIGHT: widen the frame to the whole partition
SELECT employee_id,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING) AS last_salary
FROM employees;

If a query used LAST_VALUE correctly in Oracle, it already had the explicit frame and it carries straight over. The danger is code that was subtly wrong in Oracle too — migration is a good moment to fix it rather than faithfully reproduce a bug.

Oracle-Only: KEEP (DENSE_RANK FIRST/LAST)

Oracle’s KEEP … (DENSE_RANK FIRST/LAST ORDER BY …) is a genuinely Oracle-specific aggregate — a compact way to say “of the rows in this group, give me the MAX(salary) belonging to the earliest hire_date.” PostgreSQL has no KEEP clause, so this is one of the few window-adjacent constructs you must actually rewrite.

-- Oracle: salary of the most-recently-hired employee per department
SELECT department_id,
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) AS newest_hire_salary
FROM employees
GROUP BY department_id;

-- PostgreSQL: a window function + DISTINCT ON does the same job
SELECT DISTINCT ON (department_id)
       department_id,
       salary AS newest_hire_salary
FROM employees
ORDER BY department_id, hire_date DESC;

DISTINCT ON is a PostgreSQL superpower Oracle lacks: it keeps the first row per group given an ORDER BY, which covers most KEEP use cases in one clean statement. For multiple aggregates in the same group, a subquery with ROW_NUMBER() filtered to = 1 is the general-purpose rewrite.

IGNORE NULLS: Native Since PostgreSQL 17

Oracle has long let you write LAG(x) IGNORE NULLS or LAST_VALUE(x IGNORE NULLS) to skip over nulls when carrying a value forward — the classic “last known price” pattern. This was a real gap in PostgreSQL for years, and PostgreSQL 17 closes it: IGNORE NULLS / RESPECT NULLS now work on lead, lag, first_value, last_value and nth_value.

-- PostgreSQL 17+: direct equivalent of Oracle's IGNORE NULLS
SELECT ts, price,
       LAST_VALUE(price) IGNORE NULLS OVER (ORDER BY ts
                                            ROWS BETWEEN UNBOUNDED PRECEDING
                                                     AND CURRENT ROW) AS last_known_price
FROM ticks;

-- Pre-17 workaround: bucket by a running count of non-null values
SELECT ts,
       MAX(price) OVER (PARTITION BY grp) AS last_known_price
FROM (
  SELECT ts, price,
         COUNT(price) OVER (ORDER BY ts) AS grp   -- only increments on non-null
  FROM ticks
) s;

If your target is PostgreSQL 17 or newer — and for a fresh migration in 2026 it should be — you can port IGNORE NULLS as-is. On an older target, the running-count bucketing trick above is the portable rewrite.

Two Smaller Ones: RATIO_TO_REPORT and the WINDOW Clause

Oracle’s RATIO_TO_REPORT(x) has no PostgreSQL function, but it’s just a share-of-total — one line with a plain window SUM:

-- Oracle:  RATIO_TO_REPORT(amount) OVER (PARTITION BY region)
-- PostgreSQL:
SELECT region, amount,
       amount::numeric / SUM(amount) OVER (PARTITION BY region) AS ratio
FROM sales;

And a quality-of-life win worth adopting while you’re rewriting: PostgreSQL (and Oracle) support a named WINDOW clause, so a query that reuses the same partition three times states it once. It makes the migrated SQL easier to read than the original — the “better SQL” in the title.

SELECT employee_id,
       RANK()       OVER w AS dept_rank,
       DENSE_RANK() OVER w AS dept_dense_rank,
       AVG(salary)  OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);