How to add new column based on calculation of another column in postgresql?

Question

I have the following table:

id time_start area
1 2019年09月01日T10:12:32Z london
2 2019年08月29日T10:13:32Z chicago
3 2019年07月31日T10:14:32Z paris
4 2019年09月28日T10:17:32Z madrid
5 2019年07月04日T10: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!

• user1822: What's wrong with select id, time_start::date, area from ...?
• Akina: 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.
• user194514: how can i use the update query with regards to this question?

Answer 1

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?

• Olayinka: Perhaps if you want to migrate a column to another data type

Answer 2

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!