Обсуждение: Assistance in importing a csv file into Postgresql

Dear List

I have a csv file that I would like to import into Postgresql, the
structure of the csv file is in this format:
"field1","field2","field3","field4"
"1","2","RD","00001"
"2","2","RD","00001"
"4","2","RD","00001"
"4","2","RD","00001"
"5","2","RD","00001"
"5","2","RD","00001"
"5","2","RD","00001"
"6","2","RD","00001"
"1","3","RD","00003"
"2","3","RD","00003"
"3","3","RD","00003"
"4","3","RD","00003"
"4","3","RD","00003"
"5","3","RD","00003"
"5","3","RD","00003"

What I would like to do is

If field1=1 make table1 and insert the rest of field1=1 into this table
If field1=2 make table2 and insert the rest of field1=2 into this table

Hence in this example one will have table1, table2, table3, table4,
table5 and table6

How can I accomplish this using the COPY command.

Postgresql is running on a Windows platform.

Thanks in advance.

--
Sindile Bidla

what's the postgrtesql version there ?
(just do "select version();", or psql --version);

Greetings,

* Intengu Technologies (sindile.bidla@gmail.com) wrote:
> What I would like to do is
>
> If field1=1 make table1 and insert the rest of field1=1 into this table
> If field1=2 make table2 and insert the rest of field1=2 into this table
>
> Hence in this example one will have table1, table2, table3, table4,
> table5 and table6
>
> How can I accomplish this using the COPY command.

This currently can't be done with the COPY command directly.  There are
a couple of options:

#1- Have a single table with a trigger on it that does this for you
#2- Write a simple perl script which does this for you
#3- Load the data into one table and then use SQL to move it to the
    other tables (which you would need to create first)

Enjoy,

    Stephen

On Mon, May 25, 2009 at 4:12 PM, Stephen Frost <sfrost@snowman.net> wrote:

> This currently can't be done with the COPY command directly.  There are
I would put it in postgresql as is, and than do "CREATE TABLE foo AS
SELECT .... CASE ... END ;"



--
GJ

* Grzegorz Jaśkiewicz (gryzman@gmail.com) wrote:
> On Mon, May 25, 2009 at 4:12 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> > This currently can't be done with the COPY command directly.  There are
> I would put it in postgresql as is, and than do "CREATE TABLE foo AS
> SELECT .... CASE ... END ;"

Right, that would be option #3 from my list. :)

    Stephen

2009/5/25 Stephen Frost <sfrost@snowman.net>:
>
> Right, that would be option #3 from my list. :)
Aye,
The reason I am asking about version, is because 8.1 can't import CSV
using COPY.



--
GJ

Version 8.3.7

On 25/05/2009, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> what's the postgrtesql version there ?
> (just do "select version();", or psql --version);
>


--
Sindile Bidla

Thanks for the pointers will try them.

On 25/05/2009, Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> * Intengu Technologies (sindile.bidla@gmail.com) wrote:
>> What I would like to do is
>>
>> If field1=1 make table1 and insert the rest of field1=1 into this table
>> If field1=2 make table2 and insert the rest of field1=2 into this table
>>
>> Hence in this example one will have table1, table2, table3, table4,
>> table5 and table6
>>
>> How can I accomplish this using the COPY command.
>
> This currently can't be done with the COPY command directly.  There are
> a couple of options:
>
> #1- Have a single table with a trigger on it that does this for you
> #2- Write a simple perl script which does this for you
> #3- Load the data into one table and then use SQL to move it to the
>     other tables (which you would need to create first)
>
> Enjoy,
>
>     Stephen
>


--
Sindile Bidla