0

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

psygo
  • 95
  • 2
  • 10
  • What did you tried? – Jaisus Mar 31 '20 at 11:49
  • I am a beginner, so I do not have much knowledge of Postgresql. I have selected the route from the table name and used an alter table query to insert a new column in it, but I am not getting any idea about, how to convert the data in the required format. – psygo Mar 31 '20 at 12:04

1 Answers1

1

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)

Enlico
  • 23,259
  • 6
  • 48
  • 102
Bjarni Ragnarsson
  • 1,731
  • 1
  • 6
  • 8