Обсуждение: Problem importing a csv file
Hi,
I am attempting to import a csv file into a predefined, empty table using the following commands:
COPY ContactUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
WITH DELIMITERS ',' CSV
When testing the query I get the following error message
ERROR: syntax error at or near "COPY"
LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY Conta...
^
********** Error **********
ERROR: syntax error at or near "COPY"
SQL state: 42601
Character: 59
I have been able to determine what the error is. Researching the question does not give me any clue what is wrong or what the syntax error is.
Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII
I am attempting to import a csv file into a predefined, empty table using the following commands:
COPY ContactUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
WITH DELIMITERS ',' CSV
When testing the query I get the following error message
ERROR: syntax error at or near "COPY"
LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY Conta...
^
********** Error **********
ERROR: syntax error at or near "COPY"
SQL state: 42601
Character: 59
I have been able to determine what the error is. Researching the question does not give me any clue what is wrong or what the syntax error is.
Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII
| -- Jay Lozier jslozier@gmail.com |
What if you run this query using psql? -- ------------ pasman
On Saturday, August 27, 2011 10:03:53 pm planas wrote: > Hi, > > I am attempting to import a csv file into a predefined, empty table > using the following commands: > > COPY ContactUpdates > FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv' > WITH DELIMITERS ',' CSV > > When testing the query I get the following error message > > ERROR: syntax error at or near "COPY" > LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY > Conta... > ^ > > > ********** Error ********** > > ERROR: syntax error at or near "COPY" > SQL state: 42601 > Character: 59 > > I have been able to determine what the error is. Researching the > question does not give me any clue what is wrong or what the syntax > error is. Is the above the only command being run? Where is this coming from?: LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )... > > Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII -- Adrian Klaver adrian.klaver@gmail.com
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote:
It did import the data and it gave no error messages
What if you run this query using psql? -- ------------ pasman
It did import the data and it gave no error messages
| -- Jay Lozier jslozier@gmail.com |
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote:
When I run the following:
DROP Table "ContactUpdates"
CREATE TABLE "ContactUpdates"
(
"VendorID" integer NOT NULL,
"LastName" character varying(45),
"FirstName" character varying(45),
CONSTRAINT "ContactUpdates_pkey" PRIMARY KEY ("VendorID" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE "ContactUpdates"
OWNER TO postgres;
The table is created with proper columns
Then running
COPY CountUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV
gives this error message
ERROR: relation "countupdates" does not exist
********** Error **********
ERROR: relation "countupdates" does not exist
SQL state: 42P01
Adding ""
COPY "CountUpdates"
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV
ERROR: relation "CountUpdates" does not exist
********** Error **********
ERROR: relation "CountUpdates" does not exist
SQL state: 42P01
But the file exists, it was previously created. What is the problem?
What if you run this query using psql? -- ------------ pasman
When I run the following:
DROP Table "ContactUpdates"
CREATE TABLE "ContactUpdates"
(
"VendorID" integer NOT NULL,
"LastName" character varying(45),
"FirstName" character varying(45),
CONSTRAINT "ContactUpdates_pkey" PRIMARY KEY ("VendorID" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE "ContactUpdates"
OWNER TO postgres;
The table is created with proper columns
Then running
COPY CountUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV
gives this error message
ERROR: relation "countupdates" does not exist
********** Error **********
ERROR: relation "countupdates" does not exist
SQL state: 42P01
Adding ""
COPY "CountUpdates"
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV
ERROR: relation "CountUpdates" does not exist
********** Error **********
ERROR: relation "CountUpdates" does not exist
SQL state: 42P01
But the file exists, it was previously created. What is the problem?
| -- Jay Lozier jslozier@gmail.com |
On Mon, Aug 29, 2011 at 11:21 AM, planas <jslozier@gmail.com> wrote:
CREATE TABLE "ContactUpdates"
(
"VendorID" integer NOT NULL,
"LastName" character varying(45),
"FirstName" character varying(45),
CONSTRAINT "ContactUpdates_pkey" PRIMARY KEY ("VendorID" )
ERROR: relation "countupdates" does not exist
SQL state: 42P01
Adding ""
COPY "CountUpdates"
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV
ERROR: relation "CountUpdates" does not exist
Table you created is "ContactUpdates" and you are trying to COPY data into "CountUpdates"? why do you think the error is not what you expected??
cheers,
Shoaib
On Sunday, August 28, 2011 5:48:50 pm you wrote: > > > error is. > > > > Is the above the only command being run? Where is this coming from?: > > > > LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )... > > Explain mode, in pgadminIII > > > > Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII As far as I know EXPLAIN does not work with COPY. From the docs: http://www.postgresql.org/docs/9.0/interactive/sql-explain.html " statement Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE TABLE AS statement, whose execution plan you wish to see. " -- Adrian Klaver adrian.klaver@gmail.com