Thread: incomplete CTE declaration and "column reference x is ambiguous"

Marc Mamin-2 wrote
> Hello,
>
> I've lost some time to debug a large Query with many CTE.
> I couldn't  really believe the error message.
>
> it was correct after all , though surprising.
>  a short version to illustrate my error:
>
> WITH t1 (a,b) AS (
>          SELECT
>                         1 as x,
>                 2 as a,
>                 3 as b
>         )
> select * from t1 WHERE b =0
>
> ERROR: column reference "b" is ambiguous.
>
>
> It would be nice, if extra undeclared columns would not be visible outside
> the CTE.
>
> regards,
>
> Marc Mamin

That ship has already sailed.

At least this way you know you are confused somewhere (or missed changing
something).  If you only want two output columns you should modify the query
to explicitly state which two you want.

If anything I'd rather enforce an "all-or-nothing" approach where your query
throws an "Invalid CTE Definition - the number of declared columns does not
match the actual column count" error instead.  If I add a column in the
middle of the SELECT list and forget to change the output columns the error
will tell me I forgot something instead of simply re-aliasing all of my
columns and then continuing as if nothing is wrong.

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/incomplete-CTE-declaration-and-column-reference-x-is-ambiguous-tp5762037p5762048.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.