CODEWITHSUNDEEP
postgresqlsql-updateddlalter-table
psygo
psygo

Reputation: 123

How to add a column in the existing table and update all the existing data in postgresql?

I have an existing table, I want to insert a new column and update values in the whole table so that I do not have to refill the table again.

But the problem is, I have a route column which is present in the format shown below. I want to add a new column route_name where I will not include data after the 2nd underscore '_'

How do I do this by running a query?

This is how the table looks like.

route                                     route_name  (should look like)

dehradun_delhi_09:30_am                        dehradun_delhi        
katra_delhi_07:30_pm                           katra_delhi                                 
delhi_katra_08:00_pm                           delhi_katra  
bangalore_chennai_10:45_pm                     bangalore_chennai 
delhi_lucknow_09:00_pm                         delhi_lucknow  
chennai_bangalore_10:30_pm                     chennai_bangalore 
lucknow_varanasi_10:30_pm                      lucknow_varanasi
varanasi_lucknow_09:30_pm                      varanasi_lucknow  
delhi_katra_08:00_pm                           delhi_katra
katra_delhi_07:30_pm                           katra_delhi
delhi_jalandhar_10:00_pm                       delhi_jalandhar
jalandhar_delhi_11:00_am                       jalandhar_delhi
delhi_amritsar_11:00_pm                        delhi_amritsar   
amritsar_delhi_11:00_pm                        amritsar_delhi

Please tell me what query should I run so that the data backfilled also gets updated and a new column called route_name gets updated in the existing table

Upvotes: 0

Views: 47

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

You need to do this in two steps. First you add the column:
alter table route_table add column route_name text;
and then populate it:
update route_table set route_name=split_part(route,'_',1)

Upvotes: 1

Related Questions