Thread: copy command in Windows XP
Hey folks!
First off - been using postgres for a short time and am really impressed! Much more professional than mysql. When I saw a query plan that actually looked like a query plan I was very pleased! (maybe I'm being a bit harsh on mysql here?)
I'm running postgres 8.3 (just downloaded last week) on my dev machine (Windows Xp). I have what is probably a very simple question. I'm trying to do a bulk insert of up to about 1000 rows using the copy command. I'm using php's pdo for the data access. Here's some example sql I'm sending to the server:
COPY searchindustry (searchsessionid, industrycode) FROM stdin;
289 5110
289 5121
289 5122
289 5123
289 5124
289 5125
289 5126
289 5129
\.
The error I get in the postgres query log is "syntax error at or near "289" at character 65". char 65 is the first line of the data.
The data columns are seperated by a tab (they're not shown in this email properly). Since it's Windows all lines are terminated with both CR and LF. searchindustry is a table with 3 columns. The 2 you can see are integer and the 3rd column is a serial.
I've examined the data in hex and there are no weird chars in it.
I've pasted the same query into the sql tool in pgadmin 3 and I get exactly the same error.
As a side note, if I try to use the copy command to output from a table to stdout the query never completes (pgadmin 3 crashes and I have to kill it and restart). This is probably a pgadmin 3 bug.
Any help would be much appreciated.
Thanks!
Mat Dodgson
First off - been using postgres for a short time and am really impressed! Much more professional than mysql. When I saw a query plan that actually looked like a query plan I was very pleased! (maybe I'm being a bit harsh on mysql here?)
I'm running postgres 8.3 (just downloaded last week) on my dev machine (Windows Xp). I have what is probably a very simple question. I'm trying to do a bulk insert of up to about 1000 rows using the copy command. I'm using php's pdo for the data access. Here's some example sql I'm sending to the server:
COPY searchindustry (searchsessionid, industrycode) FROM stdin;
289 5110
289 5121
289 5122
289 5123
289 5124
289 5125
289 5126
289 5129
\.
The error I get in the postgres query log is "syntax error at or near "289" at character 65". char 65 is the first line of the data.
The data columns are seperated by a tab (they're not shown in this email properly). Since it's Windows all lines are terminated with both CR and LF. searchindustry is a table with 3 columns. The 2 you can see are integer and the 3rd column is a serial.
I've examined the data in hex and there are no weird chars in it.
I've pasted the same query into the sql tool in pgadmin 3 and I get exactly the same error.
As a side note, if I try to use the copy command to output from a table to stdout the query never completes (pgadmin 3 crashes and I have to kill it and restart). This is probably a pgadmin 3 bug.
Any help would be much appreciated.
Thanks!
Mat Dodgson
"Mat Dodgson" <mat@mad.id.au> writes:
> Here's some example sql I'm sending to the server:
> COPY searchindustry (searchsessionid, industrycode) FROM stdin;
> 289 5110
> 289 5121
> 289 5122
> 289 5123
> 289 5124
> 289 5125
> 289 5126
> 289 5129
> \.
> The error I get in the postgres query log is "syntax error at or near "289"
> at character 65". char 65 is the first line of the data.
How are you "sending" this exactly? The error message suggests that
it's all being pushed at the server as one SQL string. That won't work.
The correct implementation requires just sending the COPY command itself
to the server, waiting for it to switch into COPY mode, and then sending
the data (using a different protocol than SQL commands are sent with).
You didn't say, but I gather you're using pgAdmin. I don't know whether
it has any support for copy-from-stdin, but if it does I'm pretty sure
you'd have to provide the data separately from the command. If you
can't find anything like that, you'll have to resort to putting the data
in a file and using copy-from-file instead.
regards, tom lane