如何在PostgreSQL的CTE或子查询中引用DISTINCT后的列名？

The issue here is how you’re using DISTINCT. When you write DISTINCT(col1, col2), PostgreSQL interprets this as creating a single composite row column (containing both col1 and col2 values) rather than selecting distinct pairs of the two separate columns. That’s why you can’t reference col1 or col2 directly later—those columns don’t exist in your CTE/subquery; instead, there’s one column with a row type.

Here’s how to fix it:

Corrected CTE Approach #

Remove the parentheses around col1 and col2 to get distinct pairs as separate columns:

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;

Corrected Subquery Approach #

Same fix applies here—use DISTINCT with the columns listed separately:

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;

Alternative: Using DISTINCT ON (If You Need Specific Rows) #

If you ever need to pick a specific row for each unique (col1, col2) pair (e.g., the latest row), you can use PostgreSQL’s DISTINCT ON feature:

SELECT json_build_object('col1', col1, 'col2', col2)
FROM (
  SELECT DISTINCT ON (col1, col2) col1, col2
  FROM table1
  WHERE col1 IS NOT NULL AND col2 IS NOT NULL
  ORDER BY col1, col2 -- Add additional columns here to prioritize which row to keep
) AS temp;

This returns the first row for each unique combination of col1 and col2, ordered by the clauses you specify.

内容的提问来源于stack exchange，提问作者Abe Voelker