Question 296386 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Hot to use SQL WITH VALUES clause to define constant value

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"

Tags:

4

Answers


  1. 0

    If you want to select from some values, rather than just creating a table and inserting into it, you can do something like:

    WITH  params (v) AS (VALUES (0), (10)) 
SELECT * 
FROM params ;

    This will outputs :

    v
0
10

    To join this dataset with your current table you can do something like :

    WITH  params (v) AS (VALUES (0), (10)) 
SELECT w.* 
FROM weather_data w
INNER JOIN params p on w.parameter_type = p.v
    Login or Signup to reply.
  2. 0

    With clause is used to define named result sets or CTEs.
    In your case, the ParameterType is not accessible in the where clause.

    Try something like this.

    WITH weatherData AS (
  SELECT *
  FROM weather_data
  WHERE parameter_type = 'TEMPERATURE'
)
SELECT *
FROM weatherData
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0

    You don’t need the cte at all: demo at db<>fiddle

    SELECT *
FROM weather_data,(VALUES ('TEMPERATURE'))_(ParameterType)
WHERE parameter_type = ParameterType;

    This spawns a single-value table the same way and joins it with your table the same way.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search