0

I have two columns: year and month:

Year    Month
2017    01
2017    02
2018    12
2019    06
2020    07

With

select to_date(concat(Year, Month), 'YYYYMM') csv_date FROM my_table;

I can get just one column with date datatype.

How can I add this column in my table, to get this:

Year    Month   csv_date
2017    01      2017-01-00
2017    02      2017-02-00
2018    12      2018-12-00
2019    06      2019-06-00
2020    07      2020-07-00
Rafael Higa
  • 655
  • 1
  • 8
  • 17
  • Do you want to add new column to table or just select from table as you show in your last result? – Abdusoli Jul 22 '20 at 16:07
  • I want to add a new column. Now I know only how to make these values with a `select`, but I don't know how to insert it in the table. Everything I've read wasn't exactly what I want, and I didn't understand too. I would like to do something equivalent as in pandas would be `df['new_column'] = df.apply(lambda row: a_function_to_convert_to_date(row['Year'], row['Month']]))` – Rafael Higa Jul 22 '20 at 16:48

2 Answers2

2

You can not have a column defined as date that contains 00 for the day. That would be an invalid date, and Postgres will not allow it. The suggested method of concatenating the 2 works if the year and month are defined as a string type column, but the result will have '01' for the day. If those columns are defined as numeric then you can use the make date function.

with my_table(tyr, tmo, nyr,nmo) as 
   ( values ('2020', '04', 2020, 04 ) )
select to_date(concat(tyr, tmo), 'YYYYMM') txt_date
     , make_date(nyr,nmo,01) num_date  
  from my_table;

With that said then use the to_char function for a date column you can to get just year and month (and if you must) add the '-00'. so

with my_table (adate) as 
   ( values ( date '2020-04-01') )
select adate, to_char(adate,'yyyy-mm') || '-00' as yyyymm
  from mytable;

If you are on v12 and want to add the column you can add it as a generated column. This will have the advantage that it cannot be updated independently but will automatically update when the source columns(s) get updated. See fiddle complete example;

alter table my_table add column cvs_date date generated always as (make_date(yr, mo,01)) stored;
Belayer
  • 13,578
  • 2
  • 11
  • 22
1

Using PostgreSQL Query

If you want to add new column then

alter table my_table add column csv_date date;

update my_table set csv_date=to_date(concat(Year, Month), 'YYYYMM');

If you want only select output then:

select year, month, to_date(concat(Year, Month), 'YYYYMM') csv_date FROM my_table;
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32