Splitting text column to multiple rows

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Splitting text column to multiple rows
Date: 2010-03-28 17:43:14
Message-ID: 7055D37C1A764A1EA8E8E40E0EEED21D@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

TEXT column contains multi-line text.
How to split it to multiple rows so that every line is in separate row ?
Code below should return two rows,

Line 1
Line 2

Solution should work starting at 8.1

Should generate_series or pgsql procedure used or any other idea?

Andrus.

create temp table test ( test text ) on commit drop;
insert into test values( 'Line 1' ||chr(13)||'Line2');

create temp table test2 ( test text ) on commit drop;
-- todo: split test to multiple rows
insert into test2 select * from test;
select * from test2;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2010-03-28 18:03:44 Re: Splitting text column to multiple rows
Previous Message Andrus 2010-03-28 17:43:07 How to perform text merge