[FIXED] How to split name column into firstName and lastName columns in PostgreSQL?

Issue

Assume this User table in PostgreSQL:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY,
    email text NOT NULL,
    name text,
);

I want to split name into firstName and lastName and update the existing rows accordingly. I understand that I can use the following SQL to return the data as firstName and lastName:

SELECT 
  split_part("name", ' ', 1) AS "firstName", 
  split_part("name", ' ', 2)  AS "lastName"
FROM "User";

I guess I now need to use UPDATE somehow in order to update the existing rows, but I'm not clear on the exact syntax.

Solution

One straightforward way to do this would be:

UPDATE "User"
    SET "firstName" =  split_part("name", ' ', 1),
        "lastName" = split_part("name", ' ', 2)

(No WHERE clause because I'm assuming you want to transform the whole table that way.)

Answered By - Tom Houlé

Answer Checked By - Terry (PHPFixing Volunteer)