CODEWITHSUNDEEP
postgresql
thiebo
thiebo

Reputation: 1435

postgresql WITH statement doesn't find auxiliary statements

I have this query:

WITH words_not AS (
        SELECT keywords.id 
        FROM keywords 
        WHERE keyword = any(array['writing'])
    ),actes_not AS (
        SELECT actes_keywords.acte_id 
        FROM actes_keywords 
        WHERE actes_keywords.keyword_id IN (words_not)
    )
    SELECT  actes.id, 
            actes.acte_date 
    FROM actes 
    WHERE actes.id <> all(actes_not);

This returns the following error:

ERROR:  column "words_no" does not exist
LINE 1: ...ctes_keywords WHERE actes_keywords.keyword_id IN (mots_non))...

Each auxiliary statement in the WITH query is good (tested) and I thought I was staying pretty close to the manual: https://www.postgresql.org/docs/current/static/queries-with.html

I don't see why the auxiliary statement in the WITH query is not recognised.

Upvotes: 0

Views: 123

Answers (1)

user330315
user330315

Reputation:

You can't use a table reference in an IN (..) clause. You need a sub-query:

WITH words_not AS (
  SELECT keywords.id 
  FROM keywords 
  WHERE keyword = any(array['writing'])
), actes_not AS (
  SELECT actes_keywords.acte_id 
  FROM actes_keywords 
  WHERE actes_keywords.keyword_id IN (select id from words_not) --<< HERE
)
SELECT  actes.id, 
        actes.acte_date 
FROM actes 
WHERE actes.id <> all(select id from actes_not); --<< HERE

Upvotes: 1

Related Questions