问题描述
我正在使用Postgres 8.3.11。我有一个像这样的表:
I am using Postgres 8.3.11. I have a table like:
user | countries (varchar(100))
h | us
g | brazil,germany
我想要的是显而易见的
user | countries
h | us
g | brazil
g | germany
我创建了一个函数,该函数获取像'brazil,germany'这样的字符串,并将其作为单个输出列表:
I created a function that gets a string like 'brazil,germany' and outputs it as a single column table:
|germany|
|brazil |
我可以使用游标逐行执行该游标以获取我想要的东西,但是在那里必须是更好的SQL方式。
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.
推荐答案
(目前在8.3中为Postgres)应该使此操作变得容易:
regexp_split_to_table()
(present Postgres in 8.3) should make this easy:
SELECT usr, regexp_split_to_table(countries, ',') AS country FROM tbl;
结果:
usr | country
-----+---------
h | us
g | brazil
g | germany
使用短字符串,这仍然是PostgreSQL 9.1中的首选解决方案。
With short strings this is still the preferable solution in PostgreSQL 9.1.
regexp_split_to_table()
的性能会随着较长的字符串而降低。在8.4+版本中,请使用 unnest(string_to_array(...))
。
Performance of regexp_split_to_table()
degrades with longer strings, though. In 8.4+ use unnest(string_to_array(...))
instead.
除了PostgreSQL 8.3,旧。考虑(9.2即将发布)。
As an aside PostgreSQL 8.3 is getting old. Consider upgrading to the current version 9.1 (9.2 to be released soon).
这篇关于从字符串创建表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!