By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Copy select query result to a file

jlm699
100+
P: 314
I'm at the end of my wits here and can't possibly see what I'm doing wrong.

I tried doing a "COPY (query) TO '/var/tmp/filename.csv' WITH CSV", which gave me an error. So I tried it w/o the WITH CSV... same error. I tried the query by itself which worked. As soon as I add the parenthesis and the COPY TO stuff I get the following error:

ERROR: syntax error at or near "("
SQL state: 42601
Character: 6

Here's a simple test I devised to make sure it wasn't my actual tables.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE copytest (t1 char, t2 integer, t3 text) WITH OIDS;
  2. INSERT INTO copytest VALUES('a', 5, 'testing');
  3. INSERT INTO copytest VALUES('b', 4, 'this');
  4. INSERT INTO copytest VALUES('c', 3, 'should');
  5. INSERT INTO copytest VALUES('d', 2, 'work');
  6. INSERT INTO copytest VALUES('e', 1, 'right?');
  7. SELECT * FROM copytest;
  8.  
That works just fine... now as soon as I try to do the copy operation using the same exact query it fails. I'm sure this is something very simple that I'm messing up but I can't see where I'm going wrong.
Expand|Select|Wrap|Line Numbers
  1. -- This doesn't work
  2. COPY (SELECT * FROM copytest) TO '/var/tmp/copytest.csv' WITH CSV;
  3. -- This doesn't work either
  4. COPY (SELECT * FROM copytest) TO '/var/tmp/copytest.csv';
  5. -- Atleast this works, but isn't quite what I'm going for
  6. COPY copytest TO '/var/tmp/copytest.csv' WITH CSV;
  7.  
The two that fail above fail with that same syntax error. As far as I can see I'm doing it the same as in the PostgreSQL guide! Can anybody help me?

Regards,

James
Jan 9 '08 #1
Share this Question
Share on Google+
2 Replies


jlm699
100+
P: 314
Whoops! Looks like that ability is new to 8.2 (I'm still running 8.0.13)

Let me update and see what happens
Jan 9 '08 #2

jlm699
100+
P: 314
Ok, well being lazy I've omitted updating to 8.2 and just did a little workaround:

Expand|Select|Wrap|Line Numbers
  1. CREATE TEMPORARY TABLE foo_bar AS some_query;
  2. COPY foo_bar TO 'foo_bar.csv' WITH CSV;
  3.  
Works just as well I guess!
Jan 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.