Copy command postgres append to csv file

Question

I have a COPY command in psycopg2 which I am running in a loop. I wanted to know how can I append to CSV rather than performing fresh copy everytime.

code:

while True:

    cur.execute("COPY (SELECT id,a,b,c,d from t1,t2 WHERE date>= TIMESTAMP TIMESTAMP %(t)s AND date < TIMESTAMP %(t)s + interval '1h' * t2.frequency) \
           TO 'path/to/file.csv' DELIMITER ',' CSV;", d)

Thanks in advance.

Could you update your question with the section of code in question? See stackoverflow.com/help/mcve and stackoverflow.com/help/how-to-ask — Eric Renouf
– Eric Renouf, Commented Jun 16, 2016 at 14:57
Updated the question. It's a pretty generic question of appending to csv file when copy command is being used in a loop. — delalli
– delalli, Commented Jun 16, 2016 at 15:05
I don't recall COPY being able to append; however, since you are already using Python, why not just combine all the outputs into a single file after? — Nick
– Nick, Commented Jun 16, 2016 at 15:19
Do you mean opening the file in append mode and then applying the "Copy" command? — delalli
– delalli, Commented Jun 16, 2016 at 15:26
No. COPY each individual file then concatenate them into one file using Python. — Nick
– Nick, Commented Jun 16, 2016 at 15:58

joop · Accepted Answer · 2016-06-17 08:24:58Z

4

This is an ugly hack (only works on unix)

COPY table_one(id)
TO PROGRAM 'cat - >/tmp/onetwo.out'
        ;

COPY table_two(id)
TO PROGRAM 'cat - >>/tmp/onetwo.out'
        ;

edited Jun 17, 2016 at 8:24

answered Jun 16, 2016 at 16:51

joop

4,5431 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

delalli Over a year ago

I didn't try this. What does it do? Can you explain it? I understand that the filename's path is /tmp/onetwo.out. Why do you have single > in first line and >> in second line?

joop Over a year ago

postgresql.org/docs/9.5/static/sql-copy.html The copy command can read/write from/to a program's stdout/stdin, instead of a file. (This assumes unix, I don't know if windows has usable pipes yet)

joop Over a year ago

The > redirects a program's output to a file. The >> does too, but appends to the file. The cat - program simply copies it's stdin to stdout.

Collectives™ on Stack Overflow

Copy command postgres append to csv file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related