Postgres select concatenated column name

Question

For the sake of example, there's five columns in a table month:

month.week1
month.week2
month.week3
month.week4
month.week5

The number of col is determined by a function

EXTRACT(WEEK FROM NOW()) - EXTRACT(WEEK FROM DATE_TRUNC('month', NOW())) + 1

How can I select the column colX? This is what I have so far

SELECT month.week || (
    EXTRACT(WEEK FROM NOW()) 
    - EXTRACT(WEEK FROM DATE_TRUNC('month', NOW())) + 1
)::text 
FROM month

But that gives me the error

ERROR: column month.week doesn't exist SQL state: 42703

Juan Carlos Oropeza · Accepted Answer · 2017-04-12 15:20:20Z

3

Use case statement

SELECT
    CASE WHEN (week expresion) = 1 THEN month.week1
         WHEN (week expresion) = 2 THEN month.week2
         WHEN (week expresion) = 3 THEN month.week3
         WHEN (week expresion) = 4 THEN month.week4
         ELSE month.week5
    END as WeekValue
FROM month

OR

SELECT
    CASE (week expresion) 
         WHEN 1 THEN month.week1
         WHEN 2 THEN month.week2
         WHEN 3 THEN month.week3
         WHEN 4 THEN month.week4
         ELSE month.week5
    END as WeekValue
FROM month

edited Apr 12, 2017 at 15:20

answered Apr 12, 2017 at 14:19

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

1 Comment

pozs Over a year ago

Or just CASE (week expression) WHEN 1 THEN ... WHEN 2 ... to avoid repeating the (really long) week expression.

Vao Tsun · Accepted Answer · 2017-04-12 14:19:41Z

0

dynamic sql sample:

t=# create table so58(i int,w1 text);
CREATE TABLE
t=# create or replace function so59(_n int) returns table (i int,w text) as $$begin
return query execute format('select i,w%s from so58',_n);
end;
$$ language plpgsql;
CREATE FUNCTION
t=# select * from so59(1);
 i | w
---+---
(0 rows)

answered Apr 12, 2017 at 14:19

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres select concatenated column name

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related