1

I have the following table:

id   time_start           area
1   2019-09-01T10:12:32Z  london
2   2019-08-29T10:13:32Z  chicago
3   2019-07-31T10:14:32Z  paris
4   2019-09-28T10:17:32Z  madrid
5   2019-07-04T10:18:32Z  spain

I would like to add a new column called correct_date which is just the time_start column:

the new column should be added to the end and look like this:

date_start
2019-09-01
2019-08-29
2019-07-31
2019-09-28
2019-07-04

however I am filtering the data before hand based on a where statement and am a bit stuck on how to alter the table after the where condition has been satisified - or which order to do the process in? I know I will have to use timestamp::date to attain the date.

my current query is

select * 
from 
my_table 
where area in ('london','chicago','paris') 

how can I add an alter table statement to the above code so that it will add a new column based on the dates of the time_start column - would I do the alter statement at the beginning?

Couldn't find a clear answer online using postgresql!

  • What's wrong with select id, time_start::date, area from ...? – a_horse_with_no_name Nov 6 '19 at 11:34
  • ALTER TABLE adds a column only. With empty (NULL) or default (if specified) value. To insert the value which you need you must use another, UPDATE, query. – Akina Nov 6 '19 at 11:34
  • how can i use the update query with regards to this question? – user194514 Nov 6 '19 at 11:37
0

I would like to add a new column ...

... I am filtering the data before hand based on a where statement

ALTER'ing a table changes the whole table.
There is no possibility for "filtering"; either every single row gets itself a shiny, new column or none of them do.

alter table t1 
add column date_start date ; 

update t1 
set date_start = time_start::date ; 

But, as others have said, why create a whole new column to store data that is readily available within the table already?

| improve this answer | |
0

I think that it's a bad idea. The data that you want to put into a column can be easily obtained without repeating the information. Select * is a bad practice. You should do the query as follows:

Select id, time_start::date, area from my_table where area in ('london','chicago','paris') 

Greetings!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy