Postgresql – Using CASE and PARTITION BY clause to create effective and thrudates

I’m new to PostgreSQL. I have been working with SQL Server, so I have limited experience with PostgreSQL. I’m trying to convert some SQL Server queries for PostgreSQL and ran into the following issue.

Suppose I have the following table

key	date	value
A	2000-01-01	1
A	2001-01-01	2
A	2002-01-01	3
B	2001-01-01	4
B	2002-01-01	5
B	2003-01-01	6

For each row I want to create two additional columns – effective_date and thru_date. effective_date is the same as date and thru_date needs to be the next date minus 1 day for the same key. If next date doesn’t exit, ‘2200-01-01’ is used for end of time. So the resultant table will look like this

key	date	value	effective_date	thru_date
A	2000-01-01	1	2000-01-01	2000-12-31
A	2001-01-01	2	2001-01-01	2001-12-31
A	2002-01-01	3	2002-01-01	2200-01-01
B	2000-01-01	4	2000-01-01	2000-12-31
B	2001-01-01	5	2001-01-01	2001-12-31
B	2002-01-01	6	2002-01-01	2200-01-01

The following query works fine in SQL Server, but runs into syntax error with PostgreSQL.

Error executing SELECT statement. ERROR: syntax error at end of input

Is there a difference in using OVER and PARTITION BY clauses for PostgreSQL? Looking at the documentation and other examples, I don’t see a difference in using the LEAD clause but that seems to be the problem in this query.

select a.key, a.date as effdate,
    case
    when LEAD(a.date) OVER (PARTITION BY a.key ORDER BY a.date) is null then '2200-01-01'::date
    else a.date + '-1 day'::interval OVER (PARTITION BY a.key ORDER BY a.date))
    end as thrudate
from table a;

Answer

you can do this instead:

select a.key, a.date as effdate,
    LEAD(a.date,1, '2200-01-02') OVER (PARTITION BY a.key ORDER BY a.date)
     - interval '1 day' as thrudate
from table a;