Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a list of eligible user ids in a temporary tables like so

create temporary table tmp_export_users as (select id from users where ...)

then refer back to that in my \copy commands like

\copy (select ... from table where user_id in (select id from tmp_export_users)) TO 'filename.csv' WITH CSV HEADER

Those are all in the same file, one per line, and running them -f I get the error that the copy commands can't see the temporary table, so I'm guessing that the client copy command must not actually use the same postgres session as psql.

Is that correct? Is there a way to change that behavior?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv'

Call:

psql -p5432 mydb -f test.sql

psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

But you wrote, you had all commands on separate lines, so that cannot be the explanation.

The exact error message and your version number would be instrumental to your question.


All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command?

Of course, the target file would have to be local to the server not the client in this case.

share|improve this answer
    
copy runs as the postgres user, \copy wraps copy to write to std out and get redirected to the file you send it to. You can also call psql, use \o to send output to a file, and then run a copy to stdout to get similar effect. –  Scott Marlowe Nov 8 '11 at 18:23
    
To be sure I ran the test in my answer with a superuser (postgres) and a dummy user. Both work for me. Same results on v8.4. –  Erwin Brandstetter Nov 9 '11 at 1:58
    
Yeah, whether or not the postgres unix user can access things like /tmp depends on things like whether or not SELinux is installed and if it lets it out of its box. The \copy or copy to stdout are definitely the two more reliable ways to use copy. –  Scott Marlowe Nov 9 '11 at 3:36
    
Thanks for the answers everyone. Looks like I neglected to end the line that created a temporary table with a semicolon, so it didn't get created. Working as expected now –  jkebinger Nov 9 '11 at 12:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.