adding date column in postgresql

Question

I have a column of order_date in a table named dates:

order_date
"2011-02-01"
"2012-03-23"
"2011-01-01"
"2011-07-04"

I'm trying to extract the dates and add it to a new column. I used the formula

select EXTRACT(day from order_date) 
from dates

to get the dates from the column.

Now to add the values in another column in I tried the following:

update dates 
   set date1 = select EXTRACT(day from order_date) from dates;

But unfortunately the above code is not working and gives me an error at the 'select' part.

I have to write a CASE statement for the all the dates below 10. The entries before or on the 10th of each month have to be treated differently from the other dates. — Omer Qureshi
– Omer Qureshi, Commented Feb 20, 2018 at 11:45

score 1 · Accepted Answer · 2018-02-20 12:18:04Z

1

Get rid of the select

update dates 
   set date1 = EXTRACT(day from order_date);

But this seems rather useless. As a general rule you should not store information that can be derived from existing data. The overhead of extracting the day from a date is so small that storing that in another column really does not make sense.

Online example: http://rextester.com/NPONE96895

edited Feb 20, 2018 at 12:18

answered Feb 20, 2018 at 11:43

user330315

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

6 Comments

Omer Qureshi Over a year ago

I have to write a CASE statement for the all the dates below 10. The entries before or on the 10th of each month have to be treated differently from the other dates

user330315 Over a year ago

@OmerQureshi: there is nothing in your original question that indicates that. Please don't add additional requirements to your question once you get an answer.

Omer Qureshi Over a year ago

I'm not asking for additional help. Someone asked me as to why I need a column and I replied I have to use a CASE statement. The purpose of this question is to get a new column with dates. Unfortunately, the code you have mentioned above does not work

user330315 Over a year ago

@OmerQureshi: the query works according to the information you provided in your question: rextester.com/NPONE96895

Omer Qureshi Over a year ago

Yes it works. I realized the problem here. I create a table with only one value. Thanks

|

Collectives™ on Stack Overflow

adding date column in postgresql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related