473,320 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Copy select query result to a file

jlm699
314 100+
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
2 12669
jlm699
314 100+
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
314 100+
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

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

Similar topics

1
by: Phil Powell | last post by:
Here is the scope of what I need to do; want: enrollment_year allowed (even if null) all of ica criteria:
8
by: Knighterrant | last post by:
I want to copy elements from one namespace to anothor, how to create the xslt? for example, the source data is: <s:mail xmlns:s="urn:source-namespace"> <s:subject>xxxx</s:subject>...
1
by: Rhanda Zak | last post by:
Hi all, my question is maybe so trivial, that I even could not find it in Google's NG seach, so I annoy you now: I simply want to copy the result of a SELECT query to another table which has...
7
by: lgbjr | last post by:
Hello All, I¡¯m using a context menu associated with some pictureboxes to provide copy/paste functionality. Copying the image to the clipboard was easy. But pasting an image from the clipboard...
2
by: jerrygarciuh | last post by:
Hi all, The following script is giving me weird problems. I have in this directory an index.php and hurricane.php. If the script gets $i = 'on' it is supposed to back up the current index...
3
by: eholz1 | last post by:
Hello PHP programmers. I had a brilliant idea on one of my pages that selects some data from my mysql database. I first set the page up to display some info and an image, just one item, with a...
6
by: Cliff72 | last post by:
I need to fill in the nulls in the batch field the value from the record immediately preceding the null one ie replace the nulls with the preceding value until I hit a record with a value in...
10
by: pbd22 | last post by:
Hi. Like the title says - how do i do this? I was given the following example: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A' The above statement threw the following error:
21
by: Leena P | last post by:
i want to basically take some information for the product and let the user enter the the material required to make this product 1.first page test.php which takes product code and displays...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.