how to query to update array column with generated another columns in postgresql?

Question

Let's say i have table

CREATE TABLE branch
( 
  active_from timestamp without time zone,
  active_until timestamp without time zone,
  active_years integer[]
)

Both active_from and active_until column was populated by timestamp value.
active_years is still empty.

Then i wanna generate the value of active_years by both active_from and active_until columns.

example data

|active_from          |active_until           |active_years
|'2013-01-22 00:00:00'|'2015-01-22 00:00:00'  |{2013,2014,2015}

Any idea how to query it? Thanks

--i know we can do this easily by developing mini app to solve the problem.--

score 3 · Accepted Answer · 2015-10-30 08:44:18Z

3

You can generate the number of years that have passed by using the generate_series() function and then aggregate them:

select active_from, active_until, array_agg(extract(year from active_year)) as active_years
from (
  select *
  from branch
    cross join lateral generate_series(active_from, active_until, interval '1' year) as x (active_year)
) t
group by active_from, active_until;

The above requires Postgres 9.3 or later because of the lateral query.

For earlier versions you can use:

select active_from, active_until, array_agg(yr) as active_years
from (
  select active_from, active_until, extract(year from generate_series(active_from, active_until, interval '1' year)) as yr
  from branch
) t
group by active_from, active_until;

But that relies on calling a set-returning function in the select list which is discouraged, so if you are on 9.4 you should prefer the lateral join.

edited Oct 30, 2015 at 8:44

answered Oct 30, 2015 at 7:34

user330315

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

1 Comment

ilyasfals Over a year ago

Thank you very much @a_horse_with_no_name. You are briliant..:). I missed generate_series() function. I've tried to solve it with for loop. lol. I am on 9.4 and your query works briliant.

Tom-db · Accepted Answer · 2015-10-30 07:25:38Z

1

I guess there are several solutions...

A solution could be using the functions EXTRACT(), generate_series() and array_agg

SELECT array_agg(years) from 
  (SELECT generate_series(_from::int,_until::int) as years FROM
     (SELECT extract(year from active_from) AS _from,
             extract(year from active_until) AS _until 
      FROM branch
  ) as foo
)foo;

answered Oct 30, 2015 at 7:25

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

1 Comment

ilyasfals Over a year ago

thanks @Tommaso Di Bucchianico. How can I missed to use generate_series() function. lol

Collectives™ on Stack Overflow

how to query to update array column with generated another columns in postgresql?

2 Answers 2

1 Comment

1 Comment

Your Answer

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

Related