Hot to use SQL WITH VALUES clause to define constant value

December 1, 2023

In PostgreSQL I have:

SELECT *
FROM weather_data
WHERE parameter_type = 'TEMPERATURE';

I want to write this as:

WITH ParameterType AS (VALUES ('TEMPERATURE'))
SELECT *
FROM weather_data
WHERE parameter_type = ParameterType;

I get error: "column "parametertype" does not exist"

>Solution :

A CTE works just like a table.

The query below is an equivalent of what you wrote and it should be obvious to you why it will not work (A table is not a valid operand for the = operator).

SELECT *
FROM SomeTable
WHERE Column1 = SomeOtherTable;

The correct approach would of course be:

SELECT *
FROM SomeTable
WHERE Column1 = (SELECT Column1 FROM SomeOtherTable);

This works only if SELECT Column1 FROM SomeOtherTable is a scalar subquery (i.e. 1 row, 1 column). If you have more than 1 row, use the IN operator:

SELECT *
FROM SomeTable
WHERE Column1 IN (SELECT Column1 FROM SomeOtherTable);

Back to your query, we want to ideally name the column of your CTE, then use it as I described above:

WITH ParameterType(ParamValue) AS (VALUES ('TEMPERATURE'))
SELECT *
FROM weather_data
WHERE parameter_type IN (SELECT ParamValue FROM ParameterType);

Note#1: your CTE only has 1 column so naming it is optional; you could also make it work with SELECT * instead of SELECT ParamValue.
Note#2: there are, of course, other ways to achieve the same thing, such as INNER JOIN, EXISTS (SELECT ...), etc. Such ways could be convenient for a CTE with more than 1 column.