Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YxptL-0007Fb-MC for pgsql-general@arkaria.postgresql.org; Thu, 28 May 2015 04:58:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YxptK-0007Bu-4u for pgsql-general@arkaria.postgresql.org; Thu, 28 May 2015 04:58:02 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YxptH-0007Bl-LJ for pgsql-general@postgresql.org; Thu, 28 May 2015 04:57:59 +0000 Received: from hogranch.com ([75.101.82.47]) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1YxptD-0001qx-LL for pgsql-general@postgresql.org; Thu, 28 May 2015 04:57:57 +0000 Received: from [192.168.0.2] (porker [192.168.0.2]) by hogranch.com (8.11.6/8.11.6) with ESMTP id t4S4vmH13869 for ; Wed, 27 May 2015 21:57:48 -0700 Message-ID: <5566A047.20803@hogranch.com> Date: Wed, 27 May 2015 21:57:43 -0700 From: John R Pierce User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.6.0 MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: [SQL] extracting PII data and transforming it across table. References:

In-Reply-To: Content-Type: multipart/alternative; boundary="------------030406000401000804080006" X-HawgScanner-Information: Please contact the ISP for more information X-HawgScanner: Found to be clean X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multi-part message in MIME format. --------------030406000401000804080006 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 5/21/2015 9:51 AM, Suresh Raja wrote: > > I'm looking at directions or help in extracting data from > production and alter employee id information while > extracting. But at the same time maintain referential > integrity across tables. Is it possible to dump data to flat > file and then run some script to change emp id data on all > files. I'm looking for a easy solution. > > Thanks, > -Suresh Raja > > > Steve: > I too would like to update the id's before dumping. can i write a sql > to union all tables and at the same time create unique key valid > across tables. it sounds like you have a weak grasp of delational database design I would have a single Employee table, with employee ID as the primary key, and any other attributes which are common to all employees, then I would have other tables which contain information about specific employee types or groups, these other tables could have their own primary key, but would reference the Employee table EmployeeID field for the common employee attributes. -- john r pierce, recycling bits in santa cruz --------------030406000401000804080006 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 5/21/2015 9:51 AM, Suresh Raja wrote:

I'm looking at directions or help in extracting data from production and alter employee id information while extracting. But at the same time maintain referential integrity across tables. Is it possible to dump data to flat file and then run some script to change emp id data on all files. I'm looking for a easy solution.

Thanks,

-Suresh Raja

Steve:

I too would like to update the id's before dumping. can i write a sql to union all tables and at the same time create unique key valid across tables.

it sounds like you have a weak grasp of delational database design

I would have a single Employee table, with employee ID as the primary key, and any other attributes which are common to all employees, then I would have other tables which contain information about specific employee types or groups, these other tables could have their own primary key, but would reference the Employee table EmployeeID field for the common employee attributes.

-- 
john r pierce, recycling bits in santa cruz

--------------030406000401000804080006--