Copy select query result to a file

[jlm699]

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.

CREATE TABLE copytest (t1 char, t2 integer, t3 text) WITH OIDS;
INSERT INTO copytest VALUES('a', 5, 'testing');
INSERT INTO copytest VALUES('b', 4, 'this');
INSERT INTO copytest VALUES('c', 3, 'should');
INSERT INTO copytest VALUES('d', 2, 'work');
INSERT INTO copytest VALUES('e', 1, 'right?');
SELECT * FROM copytest;
 

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.

-- This doesn't work
COPY (SELECT * FROM copytest) TO '/var/tmp/copytest.csv' WITH CSV;
-- This doesn't work either
COPY (SELECT * FROM copytest) TO '/var/tmp/copytest.csv';
-- Atleast this works, but isn't quite what I'm going for
COPY copytest TO '/var/tmp/copytest.csv' WITH CSV;
 

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

[jlm699]

Whoops! Looks like that ability is new to 8.2 (I'm still running 8.0.13)

Let me update and see what happens

[jlm699]

Ok, well being lazy I've omitted updating to 8.2 and just did a little workaround:

CREATE TEMPORARY TABLE foo_bar AS some_query;
COPY foo_bar TO 'foo_bar.csv' WITH CSV;
 

Works just as well I guess!