How to add new column with data provided by result of query in postgresql

Question

I have table with column data_of_birth. I want to create query where people between 0-10 will be A, and 11-18 will be B. Something like this :

+-------+-----+-------+
| name  | age | class |
+-------+-----+-------+
| betty |   9 | A     |
| carl  |  12 | B     |
+-------+-----+-------+

The problem is how to create last column with dynamic data ?

Hint: CASE expression.

Gordon Linoff
– Gordon Linoff

2020-01-17 17:43:46 +00:00
Commented Jan 17, 2020 at 17:43 — Gordon Linoff
– Gordon Linoff, Commented Jan 17, 2020 at 17:43
oki, i will try ;p

Jarek Sularz
– Jarek Sularz

2020-01-17 17:44:46 +00:00
Commented Jan 17, 2020 at 17:44 — Jarek Sularz
– Jarek Sularz, Commented Jan 17, 2020 at 17:44

GMB · Accepted Answer · 2020-01-17 17:47:15Z

1

As hinted by Gordon Linoff, you can use a case expression:

select 
    name,
    age,
    case
        when age between 0 and 10 then 'A'
        when age between 11 and 18 then 'B'
        else '?'
    end class
from mytable

Note: age is the name of a built-in Postgres date/time function, so this is not a good pick for a column name.

answered Jan 17, 2020 at 17:47

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Jarek Sularz Over a year ago

Thanks for answer, it was really helpfull. I have another question it is good solution when i'm using this function 3 times in one query? extract(YEAR from age( timestamp '2019-12-01' , date_of_birth))

The Impaler Over a year ago

@JarekSularz That's another question. Please accept this answer, and create a new one.

GMB Over a year ago

@JarekSularz: very likely, your database will optimize the expression and not compute it 3 times, so that should not be an issue.

Jarek Sularz Over a year ago

Thanks a lot and have a nice evening ;p

Collectives™ on Stack Overflow

How to add new column with data provided by result of query in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related