Create table from string

Question

I am using Postgres 8.3.11. I have a table like:

user | countries (varchar(100))
  h  | us
  g  | brazil,germany

What I would like is the obvious

user | countries
  h  | us
  g  | brazil
  g  | germany

I created a function that gets a string like 'brazil,germany' and outputs it as a single column table:

   |germany|
   |brazil |

I can use a cursor to go row by row and execute it to get what I want, but there must be a better SQL-ish way.

If you already have a function that unnests the list, why not simply join the result to the base table? Btw: it would be a lot easier with a current version of PostgreSQL. 8.3 is to be retired soon! — , Aug 01 '12 at 09:14
every row has a different result, so what do you mean by 'simply join the result'? — harelg, Aug 01 '12 at 13:07

Erwin Brandstetter · Answer 1 · 2012-08-08T21:24:24.583

regexp_split_to_table() (present Postgres in 8.3) should make this easy:

SELECT usr, regexp_split_to_table(countries, ',') AS country FROM tbl;

Result:

 usr | country
-----+---------
 h   | us
 g   | brazil
 g   | germany

With short strings this is still the preferable solution in PostgreSQL 9.1.

Performance of regexp_split_to_table() degrades with longer strings, though. In 8.4+ use unnest(string_to_array(...)) instead.

As an aside PostgreSQL 8.3 is getting old. Consider upgrading to the current version 9.1 (9.2 to be released soon).

1 Answers1