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:


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 * 
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 ...?
    – user1822
    Nov 6, 2019 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, 2019 at 11:34
  • how can i use the update query with regards to this question?
    – user194514
    Nov 6, 2019 at 11:37

2 Answers 2


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?

  • Perhaps if you want to migrate a column to another data type
    – Olayinka
    Aug 30, 2023 at 8:57

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') 


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.