PostgreSQL dynamic column selection

Question

I am struggling a bit on some dynamic postgresql : I have a table named "list_columns" containing the columns names list with "column_name" as the variable name; those column names come from an input table called "input_table".

[list_columns]

column_name
col_name_a
col_name_b
col_name_c...

[input_table]

col_name_a	col_name_b	col_name_c	col_name_d	col_name_e
value_a_1	value_b_1	value_c_1	value_d_1	value_e_1
value_a_2	value_b_2	value_c_2	value_d_2	value_e_2
...	...	...	...	...

What I'd like to do is dynamically create a new table using that list, something like this:

create table output_table as
select (select distinct(column_name) seperated by "," from list_columns) from input_table;

The resulting table would be [output_table]

col_name_a	col_name_b	col_name_c
value_a_1	value_b_1	value_c_1
value_a_2	value_b_2	value_c_2
...	...	...

I saw I should use some execute procedures but I can't figure out how to do so.

Note: I know i could directly select the 3 columns; I oversimplied the case.

If someone would be kind enough to help me on this,

Thank you, Regards, Jonathan

Unrelated to your problem, but: Postgres 9.4 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Oct 13, 2021 at 10:22

user330315 · Accepted Answer · 2021-10-13 10:34:21Z

1

You need dynamic SQL for this, and for that you need PL/pgSQL.

You need to assemble the CREATE TABLE statement based on the input_table, then run that generated SQL.

do
$$
declare
  l_columns text;
  l_sql text;
begin
  -- this generates the list of columns
  select string_agg(distinct column_name, ',')
    into l_columns
  from list_table;

  -- this generates the actual CREATE TABLE statement using the columns
  -- from the previous step
  l_sql := 'create table output_table as select '||l_columns||' from input_table';

  -- this runs the generated SQL, thus creating the output table.
  execute l_sql;
end;
$$;

If you need that a lot, you can put that into a stored function (your unsupported Postgres version doesn't support real procedures).

answered Oct 13, 2021 at 10:34

user330315

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

Collectives™ on Stack Overflow

PostgreSQL dynamic column selection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related