I have this table with a date column:

I need to extract the year and month an used, so I used this query:

select fecha, extract(month from fecha) = 3 as mes, EXTRACT(YEAR FROM fecha) = 2022 as anio, hora, folio from table;

I get:

I need only the rows where mes and anio are true, but the WHERE clause doesn't recognize mes and anio as columns of the table.

Instead, I got the following error:

ERROR:  column "mes" does not exist
LINE 1: ..._medicion from medicion where r_fisico = true and mes = true...
                                                             ^
SQL state: 42703
Character: 353

I need this values for a <input type='month' />

CodePudding user response：

An approach using sub-query.

Basically wrap your query with another select, and place the WHERE condition outside.

SELECT * FROM (
    SELECT 
        fecha, 
        EXTRACT(MONTH FROM fecha) = 3 AS mes, 
        EXTRACT(YEAR FROM fecha) = 2022 AS anio,
        hora,
        folio 
    FROM table 
) t
WHERE t.anio = true and t.mes = true;

CodePudding user response：

Your problem stems from the sequence each phase of the SQL statement is processed. The column alias is assigned as the select list is processed however where clause is processed before the select list. Thus the assigned alias does not exist when the where clause is processed. You basically have three options:

Extract the values (with alias) in a sub-select (or CTE) then select with where referencing the alias.

select * 
  from (
        select fecha
             , extract(month from fecha) = 3 as mes
             , extract(year froM fecha) = 2022 as anio
             , hora
             , folio 
          from table
        ) 
  where mes = true 
    and anio = true;

Repeat the evaluation for mes and anio (without the actual alias).

select fecha
     , extract(month from fecha) = 3 as mes
     , extract(year froM fecha) = 2022 as anio
     , hora
     , folio 
  from table 
 where extract(month from fecha) = 3
   and extract(year froM fecha) = 2022 ;

Rewrite with where referencing just the date itself.

 select fecha
     , extract(month from fecha) = 3 as mes
     , extract(year froM fecha) = 2022 as anio
     , hora
     , folio 
  from table 
 where date_trunc('month', fecha) = date '2022-03-01';

Note None tested.