Skip site navigation (1) Skip section navigation (2)

Re: Import csv file into multiple tables in Postgres

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Deepblues <deepblues(at)gmail(dot)com>
Subject: Re: Import csv file into multiple tables in Postgres
Date: 2005-03-01 17:06:34
Message-ID: 4224A11A.6030202@NarrowPathInc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Sean Davis wrote:

>
> ----- Original Message ----- From: "Andrew Hammond" 
> <ahammond(at)ca(dot)afilias(dot)info>
> To: "Deepblues" <deepblues(at)gmail(dot)com>
> Cc: <pgsql-novice(at)postgresql(dot)org>
> Sent: Sunday, February 27, 2005 9:28 PM
> Subject: Re: [NOVICE] Import csv file into multiple tables in Postgres
>
>
>> The brief answer is no, you can not import from a single csv file 
>> into multiple tables.
>>
>> If the csv file consists of two distinct sections of data, then you 
>> could of course split it into two csv files. If what you want to do 
>> is normalize existing data, then you should first import the existing 
>> data into a working table. Then you can manipulate it within the 
>> database.
>>
>> It is unlikely that you will need perl to do any of this.
>
>
> I use perl a lot for stuff like this, but have found that in most 
> cases, the easiest thing to do is to load the data into a single 
> postgresql table and then create sql for doing the selects and inserts 
> to then create the multiple tables.  This has the added advantage that 
> you get to keep a copy of the original data available in case you 
> don't put every column into the "working" database.  If you end up 
> doing this a lot, you can create a separate "loader" schema that 
> contains all of these raw csv tables in one place, not visible by most 
> users so as not to confuse the "working" schema.
>
> Sean

I do nearly exactly as Sean has suggested all the time.

We have a schema called data_transfer that contains all of the tables 
for accepting data.  (As an aside the data is written into these tables 
by a shell script using the COPY command.)  Each of the tables has a 
trigger that fires and executes a function.  The function is responsible 
for taking the data out of the data_transfer table and putting it in 
one, two or three target tables.  In addition to the advantage noted by 
Sean you can also manipulate the data during this process as well as 
check for duplicates dynamically change between INSERT and UPDATE 
commands, etc, etc.

-- 
Kind Regards,
Keith


In response to

Responses

pgsql-novice by date

Next:From: Kumar SDate: 2005-03-01 17:32:53
Subject: Please help
Previous:From: Christian HofmannDate: 2005-03-01 14:54:20
Subject: Anyarray with mixed data types inside of a PL/PLSQL function parameter

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group