PostgreSQL Discussion - copy csv eclosed by analog quotes problem superuser ?

Author

copy csv eclosed by analog quotes problem superuser ?

Eugene

2005-12-27, 9:23 am

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
IPCITYLATLONGISPDOMA
IN 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?

--

----------------
eugene

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Bruce Momjian

2005-12-27, 1:23 pm

Eugene wrote:
> 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
> IPCITYLATLONGISPDOMA
IN 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

Right.

> 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 ...

Please post the line that is generating the error, and the table schema,
the COPY syntax used, and the actual error message. Have you read the
COPY manual page?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

George Pavlov

2005-12-28, 9:23 am

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
> IPCITYLATLONGISPDOMA
IN 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?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links