sql - How to extract first and remaining words in postgres -

postgres database table contains words separated spaces in char(20) column. result select statement should table containing 2 columns.

first column should contain first word. second column should contain remaining words. example

create temp table test (name char(20)) on commit drop ; insert test values ('word11 word12 word13'), ('word21'), ('word31 word32');  select          ? firstword,         ? remainingwords test; 

should produce

firstword     remainingwords word11        word12 word13 word21 word31        word32 

what expressions can used in place of ? marks produce this. can regexp used or other solution ?

using postgresql 9.1.2

convert values array , use that:

select (string_to_array(t, ' '))[1] first_word,        (string_to_array(t, ' '))[2:99] test; 

arrays easier work strings, particularly if have lists within row. however, can convert string if using array_to_string().

in version of postgres, more accurately written as:

select (string_to_array(t, ' '))[1] first_word,        (string_to_array(t, ' '))[2:cardinality( string_to_array(t, ' ') )] test; 

in postgres 9.6, shorthand supported:

select (string_to_array(t, ' '))[1] first_word,        (string_to_array(t, ' '))[2:] test;