How to migrate name column to first and last name in PostgreSQL-CodePudding

I want to write an SQL migration to split a "name" column to a "first_name" and a "last_name" column in a "users" table, I have already created the 2 columns.

I am looking for a command that looks like

UPDATE users
SET (first_name, last_name)
VALUES ({expression for first name}, {expression for last name});

I am accepting the assumption that the frontier between the 2 parts is the first whitespace character.

I have tried to use regexp_match(name, [^\s]*) for the first name but for name="John Doe" it returns first_name="{John}", how could I fix this, or is there a better way than using regexp_match?

CodePudding user response：

I managed to do this using (regexp_match(name, '[^\s]*'))[1] but I'm not sure if it's the most idiomatic SQL

CodePudding user response：

UPDATE users SET first_name = (regexp_split_to_array(name, E'\s '))[1], last_name = (regexp_split_to_array(name, E'\s '))[2]