如何在PostgreSQL的换行符上将值拆分为多行？ [英] How to split a value into multiple rows on the newline char in PostgreSQL?

问题描述

我有一个名为 BookInfo 的表，其结构如下：

I have have table named BookInfo with the following structure:

|id  |book_name  |description  |
--------------------------------
|1   |book 1     |harry        |
|    |           |potter       |
|    |           |Part 2       |
|2   |...        |             |

如何拆分行（ id = 1 ）换行符上的多行中（这样 harry \n potter \n Part 2 将被分为3个不同的记录：

How could I split the row (id=1) into multiple rows on the newline character (so that harry \n potter \n Part 2 would be separated into 3 different records: harry, potter and Part 2 using a query?

要重申，结果集应如下所示：

To reiterate, the resultset would look something like this:

|id  | description   |
----------------------
|1   |harry          | 
|2   |potter         |
|3   |Part 2         |

任何帮助将不胜感激，谢谢。

Any help will be much appreciated, thank you.

推荐答案

您在寻找 regexp_split_to_table（）：

http://www.postgresql.org/docs/9.2/ static / functions-string.html

select regexp_split_to_table('hello world', E'\\s+');

hello
world

(2 rows)

这篇关于如何在PostgreSQL的换行符上将值拆分为多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！