Thread:
You need to quote the strings I believe… Postgres is finding that comma at the end of “avenue” and thinking that it’s time to move on the next field (column), but then it realizes that it hasn’t reached the end of the array and barfs.
I’m not 100% sure how to fix it – either quote the strings in the array, or escape the comma after avenue with a \
~p
-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Nabil
Sent: Thursday, 22 February 2007 04:42
To: pgsql-novice@postgresql.org
Subject: [NOVICE]
Hi everyone. I'm running postgresql 7.4 and I'm trying to import some data from a CSV file using this command:
COPY vendor (vc, vname, address, city, state, zip, phone, rent_to_vc)
FROM '/home/nabil/vendordata.csv' DELIMITER ',';
but I get an error:
ERROR: malformed array literal: "{109 URBAN AVENUE"
SQL state: 22P02
Context: COPY vendor, line 2, column address: "{109 URBAN AVENUE"
the data line is:
AB00045,GOES KING,{109 URBAN AVENUE,},WESTBURY,NY,11590-4800,800-524-1556,1
and the code for the address column:
ALTER TABLE vendor ADD COLUMN address character varying(30)[];
ALTER TABLE vendor ALTER COLUMN address SET STORAGE EXTENDED;
ALTER TABLE vendor ALTER COLUMN address SET NOT NULL;
^ copied from the SQL pane in pgAdmin
I dont know why I'm getting this error... If I remove the comma at the end of AVENUE the error goes away but I created the address column with a data type of varchar(30)[2]. Does any one know what I'm doing wrong?
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
Nabil <nabil@kramer-smilko.com> writes:
> Hi everyone. I'm running postgresql 7.4 and I'm trying to import some
> data from a CSV file using this command:
Unless I've forgotten, COPY did not support CSV format before PG 8.0.
regards, tom lane
You need to quote the strings I believe… Postgres is finding that comma at the end of “avenue” and thinking that it’s time to move on the next field (column), but then it realizes that it hasn’t reached the end of the array and barfs.
I’m not 100% sure how to fix it – either quote the strings in the array, or escape the comma after avenue with a \
~p
-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Nabil
Sent: Thursday, 22 February 2007 04:42
To: pgsql-novice@postgresql.org
Subject: [NOVICE]Hi everyone. I'm running postgresql 7.4 and I'm trying to import some data from a CSV file using this command:
COPY vendor (vc, vname, address, city, state, zip, phone, rent_to_vc)
FROM '/home/nabil/vendordata.csv' DELIMITER ',';
but I get an error:
ERROR: malformed array literal: "{109 URBAN AVENUE"
SQL state: 22P02
Context: COPY vendor, line 2, column address: "{109 URBAN AVENUE"
the data line is:
AB00045,GOES KING,{109 URBAN AVENUE,},WESTBURY,NY,11590-4800,800-524-1556,1
and the code for the address column:
ALTER TABLE vendor ADD COLUMN address character varying(30)[];
ALTER TABLE vendor ALTER COLUMN address SET STORAGE EXTENDED;
ALTER TABLE vendor ALTER COLUMN address SET NOT NULL;
^ copied from the SQL pane in pgAdmin
I dont know why I'm getting this error... If I remove the comma at the end of AVENUE the error goes away but I created the address column with a data type of varchar(30)[2]. Does any one know what I'm doing wrong?
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments