CODEWITHSUNDEEP
sqlpostgresql
harelg
harelg

Reputation: 61

Create table from string

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.

Upvotes: 0

Views: 3378

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658422

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

Upvotes: 1

Related Questions