Search code examples
pythonpostgresqlpsycopg2psycopg

Psycopg copy_expert method - How to use properly

I'm trying to run code like this:

query = "copy  (select email from my_table) TO 'STDOUT' WITH (FORMAT csv, DELIMITER '|', QUOTE '^', HEADER FALSE)"
out_file = StringIO()
cursor.copy_expert(query, out_file, size=8192)

Using the copy_expert cursor method.

But I'm getting this error:

Traceback (most recent call last):
  File "etl/scripts/scratch.py", line 32, in <module>
    cursor.copy_expert(query, out_file, size=8192)
psycopg2.ProgrammingError: must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

I'm not in a position to run it as a superuser and it seems like that shouldn't be required since I'm not touching any real files.

Solution

  • There are two variants of COPY TO:

    • COPY TO STDOUT, which streams data back to the client, and
    • COPY TO 'filename', which writes to a server-side file (requiring superuser privileges).

    Your COPY statement has quotes around the STDOUT keyword, causing it to be interpreted as a filename. Just remove them.