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! — user330315
– user330315, Commented Aug 1, 2012 at 9:14
every row has a different result, so what do you mean by 'simply join the result'? — harelg
– harelg, Commented Aug 1, 2012 at 13:07

Erwin Brandstetter · Accepted Answer · 2012-08-08 21:24:24Z

1

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).

answered Aug 1, 2012 at 20:46

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1