use this:
$ psql -Uyer_user -dyer_database -fyer_copy_script.sql <
yer_data_file.csv
where yer-copy-script.sql is:
-- -------------------------------------
drop table foo;
create table foo (
c01 varchar,
c02 varchar,
c03 varchar,
c04 varchar,
c05 varchar,
c06 varchar,
c07 varchar,
c08 varchar,
c09 varchar,
c10 varchar,
c11 varchar
);
\COPY foo FROM pstdin WITH csv
select * from foo;
-- -------------------------------------
if you want more control over \COPY you can issue something like this:
\COPY foo FROM pstdin WITH delimiter as ',' quote as '"' csv
although in your case that is not needed since your data file falls
under the default settings.
if your data file is truly "huge" i do recommend you use COPY instead of
\COPY because performance is better. and yes, you do have to be the
postgres user to run COPY
all of the COPY and \COPY options, by the way, are nicely explained in
the documentation.
hope this helps.
george
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Eugene
> Sent: Saturday, December 24, 2005 4:46 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] copy csv eclosed by analog quotes problem
> superuser ?
>
> hello!
> I have IP CSV, it is huge CSV with quotes
> "69110784","69111807","US","UNITED
> STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701",
> "LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> "69111808","69112831","US","UNITED
> STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
>
> In my sql there is enclosed by command, i can do this way
> mysql> LOAD DATA INFILE
> "<path>/IP-COUNTRY-REGION-CITY-LAT-LONG-ZIPCODE-ISP-DOMAIN.CSV
> " INTO TABLE
> IPCITYLATLONGISPDOMAIN FIELDS TERMINATED BY ',' ENCLOSED BY
> '"' LINES
> TERMINATED BY '\r\n';
>
> ENCLOSED BY '"' helps in mysql
>
>
> 1.in postgresql I can't do COPY WITH CSV, it says superuser needed
> 2. i can use is [\copy] but it wont help me, I get syntax error
> 3. i can't convert current csv into csv without quotes
>
> it is like a death circle ...
>
> what should I do?