Обсуждение: importing a data file without defining a table structure

What's the quickest and easiest way to load data into tables from
external files.

I've dug around a bit and it may be the case that people just don't do
what I'm looking to do, so I'm asking here.

I've got a csv file with column names in the first row.  I want to
load that into a table and have the column types and names generated
automatically.

Can this be done?  (Or do I need to first create a table with CREATE,
typing out each column name and data type manually, and then edit the
data file to remove the first row, and then copy the file into the
table using COPY?)


Thanks,
Charles

Hello,
> typing out each column name and data type manually, and then edit the
> data file to remove the first row, and then copy the file into the
> table using COPY?)
You should be able to avoid editing the file to remove the first row. Try
HEADER option
http://www.postgresql.org/docs/8.4/static/sql-copy.html
For the difficult questions, let us wait for expert opinion.
Regards,
Jayadevan





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On Jul 22, 2010, at 4:21 PM, c b wrote:

> What's the quickest and easiest way to load data into tables from
> external files.
>
> I've dug around a bit and it may be the case that people just don't do
> what I'm looking to do, so I'm asking here.
>
> I've got a csv file with column names in the first row.  I want to
> load that into a table and have the column types and names generated
> automatically.
>
> Can this be done?  (Or do I need to first create a table with CREATE,
> typing out each column name and data type manually, and then edit the
> data file to remove the first row, and then copy the file into the
> table using COPY?)

No, Postgres does not automatically create the table structure for you. But not too difficult to implement with your
favoritescripting language. I use this method to bulk load tables from other systems and then write views and functions
toconvert it to the format I need. 




John DeSoi, Ph.D.