How to reference DISTINCT column names in a CTE or subquery?

Question

I need to generate a list of unique column pairs from a table, but Postgres seems to not like my approach.

My CTE approach:

WITH cte AS (
  SELECT DISTINCT(col1, col2)
  FROM table1
  WHERE col1 IS NOT NULL
  AND col2 IS NOT NULL
)
SELECT json_build_object('col1', cte.col1, 'col2', cte.col2)
FROM cte;

Gives me this error:

ERROR: column cte.col1 does not exist
LINE 8: SELECT json_build_object('col1', cte.col1, 'col2...

I also tried a subquery:

SELECT json_build_object('col1', col1, 'col2', col2)
FROM (
  SELECT DISTINCT(col1, col2)
  FROM table1
  WHERE col1 IS NOT NULL
  AND col2 IS NOT NULL
) AS temp;

Which failed in basically the same way:

ERROR: column "col1" does not exist
LINE 1: SELECT json_build_object('col1', col1, 'col2...

It seems I can't reference the column names as normal due to the DISTINCT. What should I do instead?

Answer 1

You made the mistake to use parentheses. DISTINCT is not a function.

The parentheses are interpreted as a row constructor, so your CTE returns only a single value of type record (and with a name chosen by the database).

Remove the parentheses, and all will be well.