Selecting certain columns from a table with dates as columns

Question

I have a table where column names are like years "2020-05","2020-06", "2020-07" etc and so many years as columns.I need to select only the current month, next month and third month columns alone from this table.(DB : PostgreSQL Version 11)

But since the column names are "TEXT" are in the format YYYY-MM , How can I select only the current month and future 2 months from this table without hard-coding the column names.

Below is the table structure , Name : static_data

Required select statement is like this,The table contains the 14 months data as in the above screen shot like DATES as columns.From this i want the current month , and next 2 month columns along with their data, something like below.

SELECT "2020-05","2020-06","2020-07" from static -- SELECT Current month and next 2 months Required output:

I think that you need to use crosstab: https://stackoverflow.com/questions/3002499/postgresql-crosstab-query — William Prigol Lopes, May 09 '20 at 14:00
You should really normalize your database design and store each date on a separate row. Your current structure makes simple tasks utterly complicated. — GMB, May 09 '20 at 14:01
Table definitions and sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , May 09 '20 at 14:24
What exactly is the contents of those columns? Don't you want to see that as well? — , May 09 '20 at 14:28
@a_horse_with_no_name There are some text values, basically numbers in various formats like like 1000$, etc.. in those columns.Yes and i would need to see the data as well.That is what i was looking for. — Linu, May 09 '20 at 14:35
Your screenshot that shows what you want, does not contain that information. I don't understand how exactly the output should look like you want. Please **[edit]** your question and add a complete example. Ideally with a `CREATE TABLE` statement and some insert statements to show the sample data as [formatted text](https://meta.stackoverflow.com/a/251362) please. See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , May 09 '20 at 14:39
@a_horse_with_no_nameEdited and attached screen shot both table and required output.Can we achieve like this.I need to select the current month and next 2 months columns from the table(There are 16 columns DATES as columns in the tABLE) from which i need to select the data from 3 columns. — Linu, May 09 '20 at 15:04

score 2 · Accepted Answer · answered May 10 '20 at 08:58

2

It's nearly impossible to get the actual value of the current month as the column name, but you can do something like this:

select d.item_sku,
       d.status,
       to_jsonb(d) ->> to_char(current_date, 'yyyy-mm') as current_month,
       to_jsonb(d) ->> to_char(current_date + interval '1 month', 'yyyy-mm') as "month + 1",
       to_jsonb(d) ->> to_char(current_date + interval '2 month', 'yyyy-mm') as "month + 2"
from bad_design d
;

answered May 10 '20 at 08:58

Thanks a lot for even though its an unnecessary complicated one,the baove one works fine and thanks a lot!! I will re-design it. – Linu May 10 '20 at 09:20
@Linu: it's "unnecessary complicated" because of your broken data model – May 10 '20 at 10:42

score 0 · Answer 2 · answered May 09 '20 at 14:12

Technically, you can use the information schema to achieve this. But, like GMB said, please re-design your schema and do not approach this issue like this, in the first place.

The special schema information_schema contains meta-data about your DB. Among these is are details about existing columns. In other words, you can query it and convert their names into dates to compare them to what you need.
Here are a few hints.

Query existing column names.

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND table_name   = 'your_table'

Compare two dates.

SELECT now() + INTERVAL '3 months' < now() AS compare;

 compare                                                       
---------
 f
(1 row)

You're already pretty close with the conversion yourself.

Have fun and re-design your schema!

I need the data as well, Something like this "SELECT Curent_month,Next_month,third_month FROM static_data" — Linu, May 09 '20 at 14:19

score 0 · Answer 3 · answered May 09 '20 at 14:23

Disclaimer: this does not answer your question - but it's too long for a comment.

You need to fix the design of this table. Instead of storing dates in columns, you should have each date on a separate row.

There are numerous drawbacks to your current design:

very simple queries are utterly complicated : filtering on dates, aggregation... All these operations require dynamic SQL, which adds a great deal of complexity
adding or removing new dates requires modifying the structure of the table
storage is wasted for rows where not all columns are filled

Instead, consider this simple design, with one table that stores the master data of each item_sku, and a child table

create table myskus (
    item_sku int primary key,
    name text,
    cat_level_3_name text
);

create table myvalues (
    item_sku int references myskus(item_sku),
    date_sku date,
    value_sku text,
    primary key (item_sku, date_sku)
);

Now your original question is easy to solve:

select v.*, s.name, s.cat_level_3_name
from myskus s
inner join myvalues v on v.item_sku = s.item_sku
where 
    v.date_sku >= date_trunc('month', now()) 
    and v.date_sku < date_trunc('month', now()) + interval '3 month'

Totally understood your point! But only 16 months data os there in the table and this table is used for updating a google sheet , So thats why came up with this design.But i will surely change the structure as you mentioned. — Linu, May 09 '20 at 14:27

Selecting certain columns from a table with dates as columns

3 Answers3