Postgres : using computed variable in a SELECT statement (part 2/2)

Question

I'm having a SELECT statement as follow (doesn't work):

SELECT foo,
    extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
       --etc
    END as age_group
FROM bar

Is there a way to "inject" here the "variable" age_normin the query ?

NOTE

Asked a similar question here, but without the parameter foopresent in this question

EDIT

Tried:

SELECT foo,
    (
    SELECT t.age_norm,
    CASE 
       WHEN t.age_norm >= 0 AND t.age_norm <1 THEN '00'
       WHEN t.age_norm >= 1 AND t.age_norm <5 THEN '01-4'
       --etc
    END 
    FROM (SELECT extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm FROM bar) t
    )
   as age_group
FROM bar

But getting:

ERROR:  subquery must return only one column
LINE 10:     (  SELECT t.age_norm,
             ^
SQL state: 42601
Character: 212

CONCLUSION

Tried some of proposed solutions but either the resulting query is too slow (compared to hard coding the extract function in each where clause) or the resulting query become too complicate (it is in fact more complicate than expressed in this post with group by clauses and others things).

So will implements each when clause as follow:

WHEN (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) >=0 AND (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) <1 THEN '00' 
etc.

Thank you for your replies!

Edited the question. I'd like to be able to calculate age_normso it can be reused in the WHEN clauses — Hey StackExchange
– Hey StackExchange, Commented Jun 21, 2022 at 10:59
Ah. you probably want a lateral join or a CTE instead of a subquery. — Jasen
– Jasen, Commented Jun 21, 2022 at 11:07

klin · Accepted Answer · 2022-06-21 11:08:13Z

1

Use a derived table (a query in the FROM clause):

SELECT 
    foo,
    age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm <1 THEN '00'
       WHEN age_norm >= 1 AND age_norm <5 THEN '01-4'
    END as age_group
FROM (
    SELECT 
        foo,
        extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm
    FROM bar
    ) s

answered Jun 21, 2022 at 11:08

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hey StackExchange Over a year ago

Wanted to try but my overall query is quite big, with group by clause and surrounding clause (with data) and guessing it will, in my case the resulting query might be over complicate. Up voted as it work through. Thanks !

Jasen · Accepted Answer · 2022-06-21 11:14:44Z

1

perhaps a lateral join like this....

SELECT foo,age_group.*
FROM 
FROM bar
, LATERAL (
   SELECT t.age_norm,
   CASE 
      WHEN t.age_norm >= 0 AND t.age_norm <1 THEN '00'
      WHEN t.age_norm >= 1 AND t.age_norm <5 THEN '01-4'
      --etc
   END 
    FROM (SELECT extract(day from CAST (bar.date as TIMESTAMP) - CAST bar.birth_date as TIMESTAMP)) / 365.25 as age_norm ) t
) as age_group;

answered Jun 21, 2022 at 11:14

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

1 Comment

Hey StackExchange Over a year ago

Thank you. It seems to work but it is very slow so I interrupted it. Hard coding the calculation in each WHEN clause sounds the fastest. Thanks again for your reply

Collectives™ on Stack Overflow

Postgres : using computed variable in a SELECT statement (part 2/2)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related