WITH clause not supported in PostgreSQL 8.3?
Mladen Gogala
syntax error on 8.3. Is there a "new features" document which would
tell me the differences?
with emp1 as (select ename,
deptno,
rank() over
(partition by deptno order by sal) as salrank
from emp)
select ename,deptno,salrank from emp1 where salrank=1;
Thomas Kellerer
Mladen Gogala wrote on 23.11.2009 23:13:
> The following query executes without a problem on 8.4 but fails with
> syntax error on 8.
Yes, CTEs were introduced in 8.4
> Is there a "new features" document which would
> tell me the differences?
David Bolen
> The following query executes without a problem on 8.4 but fails with
> syntax error on 8.3. Is there a "new features" document which would
> tell me the differences?
The manual has a release notes section (Appendix E) which generally
covers any changes, though how prominent a change is featured varies.
In this case, the WITH clause is mentioned in "E.2.3.3. Queries".
Window functions (the use of rank() and OVER in your query) are also
8.4 features (mentioned in E.2.1 and E.2.3.3), so you won't be able to
just replace the WITH clause with a sub-query under 8.3.x
-- David
Mladen Gogala
> Window functions (the use of rank() and OVER in your query) are also 8.4
> features (mentioned in E.2.1 and E.2.3.3), so you won't be able to just
> replace the WITH clause with a sub-query under 8.3.x
Bummer! I would have the developers use 8.4.1 a long time ago but one of
the developers told me that pljava is not supported with 8.4.1. I
installed the RPM packages for 8.4.1 from the download site but there is,
indeed, no pljava for 8.4.1. I will try compiling it from the source.