Re: create a script which imports csv data

On Thu, Jun 28, 2012 at 2:12 PM, Berend Tober <btober@broadstripe.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.