I have following table in PostgreSQL 11.0
min age max age
1 Month 12 Months
1 Year 16 Years
1 Day 365 Days
365 Days 369 Days
N/A N/A
NULL NULL
I would like to convert the values to year. I am extracting the string after the digit and check if it is 'years', 'months' or 'days' and then convert the digit before the string to year.
I tried following query:
update tbl
set min_age =
case
when substring(min_age, '^\d+\s(.*)') ~* 'Month'
then abs(substring(min_age, '^(\d+)\s.*')/12)
when substring(min_age, '^\d+\s(.*)') ~* 'Months'
then abs(substring(min_age, '^(\d+)\s.*')/12)
when substring(min_age, '^\d+\s(.*)') ~* 'Day'
then abs(substring(min_age, '^(\d+)\s.*')/365)
when substring(min_age, '^\d+\s(.*)') ~* 'Days'
then abs(substring(min_age, '^(\d+)\s.*')/365)
when substring(min_age, '^\d+\s(.*)') ~* 'Year'
then abs(substring(min_age, '^(\d+)\s.*'))
when substring(min_age, '^\d+\s(.*)') ~* 'Years'
then abs(substring(min_age, '^(\d+)\s.*'))
end ;
update tbl
set max_age = case
when substring(min_age, '^\d+\s(.*)') ~* 'Month'
then abs(substring(min_age, '^(\d+)\s.*')/12)
when substring(min_age, '^\d+\s(.*)') ~* 'Months'
then abs(substring(min_age, '^(\d+)\s.*')/12)
when substring(min_age, '^\d+\s(.*)') ~* 'Day'
then abs(substring(min_age, '^(\d+)\s.*')/365)
when substring(min_age, '^\d+\s(.*)') ~* 'Days'
then abs(substring(min_age, '^(\d+)\s.*')/365)
when substring(min_age, '^\d+\s(.*)') ~* 'Year'
then abs(substring(min_age, '^(\d+)\s.*'))
when substring(min_age, '^\d+\s(.*)') ~* 'Years'
then abs(substring(min_age, '^(\d+)\s.*'))
end
Expected output is:
min age max age
0 1
1 16
0 1
1 1
N/A N/A
NULL NULL
Any help is highly appreciated.
Postgres has powerful interval functions. I think that a cast and justify_interval() might just work here:
select
extract(year from justify_interval(min_age::interval)) min_age,
extract(year from justify_interval(max_age::interval)) max_age
from tbl
min_yr | max_yr :----- | :----- 0 | 1 1 | 16 0 | 1 1 | 1
This is how you can get all the values you need:
select split_part(min_age, " ", 1) as min_age_value, split_part(min_age, " ", 2) as min_age_unit, split_part(max_age, " ", 1) as max_age_value, split_part(max_age, " ", 2) as max_age_unit
from tbl;
Now, you can use the above as a substring and use case-when for min_age_unit and max_age_unit in the outer query, evaluating it to 1, 12 or 365 respectively and multiplying it with the actual value.