X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5C0D252805 for ; Tue, 24 May 2005 01:42:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18794-10 for ; Tue, 24 May 2005 04:42:42 +0000 (GMT) Received: from web31114.mail.mud.yahoo.com (web31114.mail.mud.yahoo.com [68.142.201.76]) by svr1.postgresql.org (Postfix) with SMTP id 260345281A for ; Tue, 24 May 2005 01:42:41 -0300 (ADT) Received: (qmail 64695 invoked by uid 60001); 24 May 2005 04:42:37 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=as0CHRHH/fiohl6XEW6se5wN71x7ZR4GsiEwc34jO/beMlNcn4nCG7XfogyBPhL/TI61mzI42PXEwv3LmmApZdqoLoq1sCTavRfcriy9H3QRsLugNmaHu68cfswelTRxbzWwYjQdzWT1nofFpnxZPWUG6SjHNg/i9j+ikjnVYNY= ; Message-ID: <20050524044237.64693.qmail@web31114.mail.mud.yahoo.com> Received: from [69.201.140.229] by web31114.mail.mud.yahoo.com via HTTP; Mon, 23 May 2005 21:42:37 PDT Date: Mon, 23 May 2005 21:42:37 -0700 (PDT) From: Reply-To: ogjunk-pgjedan@yahoo.com Subject: Help: Function for splitting VARCHAR column and migrating its data to 2 new tables To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.071 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_YAHOO_RCVD, NO_REAL_NAME X-Spam-Level: *** X-Archive-Number: 200505/194 X-Sequence-Number: 21507 I am restructuring my DB schema and need help migrating data from 1 column of an existing table to two new tables. I have some Java code that can do this for me, but it's very slow, and I am now hoping I can migrate this data with some clever SQL instead. Here are my 3 tables: user_data (existing, old table) --------- id (PK), user_id (FK) keywords VARCHAR(256) -- this contains comma separated keywords -- e.g. "new york,san francisco, dallas, food" -- also "keywords without strings are really just 1 keyword" add_date TIMESTAMP So now I'm trying to migrate this "keywords" VARCHAR column to a more normalized schema: user_data_keyword (new lookup table to populate) ----------------- id (PK) -- I may change PK to PK(user_data_id, keyword_id) user_data_id (FK) keyword_id (FK) keyword (new table to populate) ------- id (PK) name VARCHAR(64) NOT NULL UNIQUE add_date TIMEZONE I just found http://www.postgresql.org/docs/current/static/functions-string.html , but if anyone could lend me a hand by getting me started with writing a function for this, I'd really appreciate it. Thanks, Otis