Create columns from the result of generate_series in Postgres

Question

I feel like this should be very straightforward, but I cannot seem to find a solution.

I am trying to create a plpgsql function that takes as input a start date and an end date, that then generates a series of years between the two dates that I can use as columns for my new table.

For example, if I call

my_function('2010-01-01', '2015-01-1')

I want a new table with columns 2010, 2011, 2012, 2013, 2014, and 2015.

I'm inclined to use the generate_series() function to do this, but I cannot figure out how to actually store the values it returns and pull them out to create my table with.

I'm using Postgres 9.5.3

You need to provide some code showing what you have attempted. — Nick
– Nick, Commented Aug 15, 2016 at 19:52

klin · Accepted Answer · 2016-08-15 20:05:27Z

1

Use dynamic sql with execute format.

Assuming that the new table should have text columns:

create or replace function create_table(table_name text, from_year int, to_year int)
returns void language plpgsql as $$
begin
    execute format('create table %I(%s)', 
        table_name, 
        string_agg(concat('"', y::text, '" text'), ',')) 
    from generate_series(from_year, to_year) y;
end $$;

select create_table('my_table', 2010, 2015);

\d my_table

  Table "public.my_table"
 Column | Type | Modifiers 
--------+------+-----------
 2010   | text | 
 2011   | text | 
 2012   | text | 
 2013   | text | 
 2014   | text | 
 2015   | text |

answered Aug 15, 2016 at 20:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Randy Banks Over a year ago

would this approach be easily adaptable to add on these columns to an existing table, rather than creating a new one using them?

klin Over a year ago

Sure, try something like this: format('alter table %I %s', table_name, string_agg(concat('add column "', y::text, '" text'), ','))

Clodoaldo Neto · Accepted Answer · 2016-08-15 20:14:39Z

0

select
    'create table t ("' || 
    string_agg (to_char(y,'YYYY'), '" text, "') ||
    '" text)'
from generate_series('2010-01-01'::date, '2015-01-1', '1 year') y(y)
;
                                           ?column?                                            
-----------------------------------------------------------------------------------------------
 create table t ("2010" text, "2011" text, "2012" text, "2013" text, "2014" text, "2015" text)
(1 row)

edited Aug 15, 2016 at 20:14

answered Aug 15, 2016 at 20:02

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Create columns from the result of generate_series in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related