WITH ... AS statement not working as expected

Question

I get this error:

It arises when I try to execute the following query:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select * 
from service except expensive_service;

I was trying to implement WITH ... AS (link to PostgreSQL docs).

This query gives me the desired output I'm looking for:

select * 
from service except ( 
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)

Any aid directing me where the error lies would be greatly appreciated!

Answer 1

The CTE (common table expression) is like a view and you need to treat it like any other view (or table). You need a complete SELECT statement as the second part of the EXCEPT:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select * 
from service 
except 
select *
from expensive_service;

---

Note that parentheses around the individual parts of a UNION, EXCEPT or INTERSECT are not needed (unless you need an ORDER BY for only that query, e.g. because you are using a LIMIT clause).

So your original query could (should in my opinion) also be written as

select * 
from service 
except  
select s1.*
from service s1, service s2
where s1.price > s2.price

Answer 2

This query gives me the desired output I'm looking for:

Use this instead!

SELECT *
FROM   service
WHERE  price = (SELECT min(price) FROM service)
-- OR  price IS NULL  -- add if price can be NULL

Be sure to have an index on (price) if your table is big.

The currently accepted answer has good technical information, but the actual query should never be used. Also, you would typically want EXCEPT ALL instead of EXCEPT. See:

• Select rows which are not present in other table

But don't.

Why?

The implicit CROSS JOIN in the second SELECT scales with O(N²). What does it do?

...
SELECT s1.*
FROM   service s1, service s2
WHERE  s1.price > s2.price

This returns one copy of every row for every other row in the table with a smaller price (or where price IS NULL). What for? To exclude those in the next step. Meaning, only rows with the smallest price (or price IS NULL) are not excluded. In other words:

Get all rows with the minimum price (or price IS NULL).

Related:

• What does [FROM x, y] mean in Postgres?