From: | Arjen Nienhuis <a(dot)g(dot)nienhuis(at)gmail(dot)com> |
---|---|
To: | btober(at)computer(dot)org |
Cc: | Robert Buckley <robertdbuckley(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: create a script which imports csv data |
Date: | 2012-06-28 16:01:39 |
Message-ID: | CAG6W84Jysc66=X6X0WYEmYYv1qHHvjvuDuwOa6C1zTqt58uucQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 28, 2012 at 2:12 PM, Berend Tober <btober(at)broadstripe(dot)net> wrote:
> Robert Buckley wrote:
>>
>> I have to create a script which imports csv data into postgresql
>> The csv data is automatically created from an external database
>> so I have no influence over which columns etc are downloaded.
>>
>> How can I best create a table for the import?
>>
>
> This is what I do:
>
> 1) I have a table defined for import which reflects the CSV structure of the
> data to be imported, i.e., since I know what the fields are on each line of
> the CSV, this template table has columns defined to accommodate each known
> field. This table never actually gets data written to it.
>
> 2) To import data, my script creates a temporary import table LIKE the
> import template table.
>
> 3) The script then transfers and transform the data from the temporary
> import table to another permanent table that has the structure, including a
> primary key, that is more useful for my purposes. (It omits some of the
> columns which I do not really need from the CSV, uses a different name for
> one column, and adds some reference information. You could do calculations
> here as well.)
>
> 4) The temporary import table is deleted at the end of the import session.
>
With the file_fdw you can skip some steps:
http://www.postgresql.org/docs/9.1/static/file-fdw.html
You can SELECT from the CSV file.
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2012-06-28 16:06:46 | Re: Customising pg directories |
Previous Message | Alban Hertroys | 2012-06-28 15:49:02 | Re: create database from template requires the source database to be unused |