Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 249719FB54C for ; Sat, 21 Apr 2007 20:47:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 01376-03 for ; Sat, 21 Apr 2007 20:47:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.249]) by postgresql.org (Postfix) with ESMTP id 7FA049FB50D for ; Sat, 21 Apr 2007 20:47:22 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1423883ana for ; Sat, 21 Apr 2007 16:47:21 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=sU2Tw1zvy5ovYVcPIKZORLxYOoz9B7AhOIhpQjcinWCWQqHylUB187w31ovVvlDGrZmJJemxijPnPbyQjwz7pN3+/m5+iwUT+3Cc3S7ZOtjj/BHkYyXkeQgJ2ag+rwZcxfx0D276scYR7uwu4fuM8DTERm2F1grnFYEloasJeoI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=q1tWmjuPmvgvvUteRnV3BIukALiPQAtt2GtYOVmmhoUohlW4lbqBzjcjRsGB9hBiOXqTGnlpF4tkOVV4uvaHSWip+o02Nq80bsQysxcoGluyW5vWU4mCfNpvpd6tmBAWeEihEUQfYYiwbCLlKW7q3owByrBE/RhhheaNg0OXfNY= Received: by 10.100.37.4 with SMTP id k4mr1909747ank.1177199240869; Sat, 21 Apr 2007 16:47:20 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Sat, 21 Apr 2007 16:47:20 -0700 (PDT) Message-ID: Date: Sat, 21 Apr 2007 18:47:20 -0500 From: "Aaron Bono" To: "Lista PostgreSQL SQL" Subject: Split String Into Multiple Records MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_161456_16356717.1177199240810" X-Google-Sender-Auth: 2c76c32e38291956 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/202 X-Sequence-Number: 28202 ------=_Part_161456_16356717.1177199240810 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Is there a good way to split a string into multiple records? Here is what I am trying to do... I have a table "branch" with a column "branch_num" which has a comma delimited list of numbers - the users weren't supposed to do this but they did and now I have to fix it. We want to create a new table "branch_area" and move this comma delimited list into this new table as multiple records before dropping the "branch_num" from the "branch" table. branch { branch_id bigserial primary key, branch_num varchar(255) } branch_area { branch_area_id bigserial primary key, branch_id bigint foreign key to branch, branch_num varchar(10) } I want to migrate the data something like this: insert into branch_area (branch_id, branch_num) select branch_id, -- This is the part I need help with -> split branch.branch_num on ',' from branch ; Is there a good way (or alternative way) to do this? Thanks! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_161456_16356717.1177199240810 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Is there a good way to split a string into multiple records?

Here is what I am trying to do...

I have a table "branch" with a column "branch_num" which has a comma delimited list of numbers - the users weren't supposed to do this but they did and now I have to fix it.  We want to create a new table "branch_area" and move this comma delimited list into this new table as multiple records before dropping the "branch_num" from the "branch" table.

branch {
    branch_id bigserial primary key,
    branch_num varchar(255)
}

branch_area {
    branch_area_id bigserial primary key,
    branch_id bigint foreign key to branch,
    branch_num varchar(10)
}

I want to migrate the data something like this:

insert into branch_area
(branch_id, branch_num)
select
    branch_id,
    -- This is the part I need help with -> split branch.branch_num on ','
from branch
;

Is there a good way (or alternative way) to do this?

Thanks!

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_161456_16356717.1177199240810--