inserting select values into new attribute POSTGRESQL

Question

I have some data such as below:

full_name    

R Davies
N Powell
Harry S
A J Fulham

I want to split the name from the first space and insert the values into forename and surname. so it will look like below:

forename        surname

R               Davies
N               Powell
Harry           S
A               J Fulham

The code I have written is just to create the new columns and delete the old one. I have managed to create a select statement that will view the forename but I can't get it to insert. So I need the code to insert both values into the new columns as well as get the surname.

ALTER TABLE names add forename varchar(100)
ALTER TABLE names add surname varchar(100)

UPDATE names
INSERT INTO names(forename)
SELECT split_part(full_name, ' ', 1) FROM names

ALTER TABLE names DROP COLUMN full_name;

user330315 · Accepted Answer · 2016-02-24 14:41:42Z

1

You only want an UPDATE:

UPDATE names
   set forename = split_part(full_name, ' ', 1), 
       surname = split_part(full_name, ' ', 2)

insert is only used to create (insert) new rows.

answered Feb 24, 2016 at 14:41

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Unfitacorn Over a year ago

Thank you kinda worked! It was able to put the values into the correct place but when a full name is A J Fulham or similar then the forename is A and the surname is just J is there a way to get all of the part after the first space?

user330315 Over a year ago

@Unfitacorn: please create a new question for that.

Collectives™ on Stack Overflow

inserting select values into new attribute POSTGRESQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related