Simplest way to split human name in postgres?

Question

Consider a table with full human names:

create table names (full_name varchar not null);   
insert into names (full_name) 
  values ('Jane Marie Doe'), ('John Doe');

In postgres, what is the simplest (most readable) way to split the names into first and last?

 first_name | last_name 
------------+-----------
 Jane Marie | Doe
 John       | Doe
(2 rows)

This is what I came up with, but it seems really complicated. Surely there's a simpler way?

select 
  array_to_string(ary[1:len - 1], ' ') as first_name, 
  ary[len] as last_name 
from (
  select ary, array_length(ary, 1) as len
  from (
    select regexp_split_to_array(full_name, E'\\s+') as ary 
    from names
  ) sub1
) sub2;

Answer 1

You could try

SELECT regexp_split_to_array(full_name, E'\\s\\S+') as ary

instead, as that should split on the last space consistently. Then you know there will only be two members of ary

EDIT: On further reflection, I think the best approach might be to use

SELECT regexp_replace(full_name,'\s\S+','') as first_name
SELECT regexp_replace(full_name,'.+[\s]','') as last_name