Postgres split full name to 3 parts First Middle Last

Question

All, I have a non-trivial assignment. Due to DB structure change I need to split full names, which are sitting in one field usr_name, to 3 fields usr_firstname,usr_middlename, usr_middlename.

So far it looks easy, I can do something like this:

update app_user
   set usr_firstname  = SPLIT_PART(usr_name, ' ',1),
       usr_middlename = SPLIT_PART(usr_name, ' ',2),
       usr_middlename = regexp_replace(usr_name, '^.* ', '') ;

However, the trick is in data quality :(

Sometimes full names don't have a middle name (e.g. Vasya Pupkin), whereas sometimes it has a middle name (Vasya Vasyavich Pupkin).

I can easily get 1 name in the sting and the last word in the string, but how do I retrieve the middle (if it exists) or ignore it (if it doesn't)?

Thanks :)

I am using Postgres 9.6

UPDATE: example of table and data in

create temporary table app_user

(
    usr_name text ,
    usr_firstname   text,
    usr_middlename text,
    usr_lastname text
);

insert into app_user
select 'Adam Chwesik ', null, null, null union all
select 'Vasya Pupkin', null, null, null union all
select 'Vasya Vasyavich Pupkin', null, null, null union all
select 'Aladdin Ali Ababwa', null, null, null ;

select * from app_user;

Answer 1

The split_part function returns an empty string if the requested part doesn't exist, so assuming it's just the middle name that's optional you can simply use a case expression to test:

select split_part(user_name,' ',1) First_Name,
case when split_part(user_name,' ',3) ='' then '' else split_part(user_name,' ',2) end middle_name,
case when split_part(user_name,' ',3) ='' then split_part(user_name,' ',2) else split_part(user_name,' ',3) end last_name
from t

See example fiddle

To update your example table:

update app_user set
usr_firstname=split_part(usr_name,' ',1),
usr_middlename=case when split_part(usr_name,' ',3) ='' then '' else split_part(usr_name,' ',2) end,
usr_lastname=case when split_part(usr_name,' ',3) ='' then split_part(usr_name,' ',2) else split_part(usr_name,' ',3) end

Working fiddle