copy command in postgresql to append data

Question

I am executing copy command from a function

execute 'copy (select * from tableName) to ''/tmp/result.txt'''

It works fine. But, tableName is dynamic and will be having more tables (iterating in a loop). result.txt is having only the last iteration (table) data.

I could do, if I had to, via the command line with \o or >> by appending to a file. But, I have to use the function.

I read http://shuber.io/reading-from-the-filesystem-with-postgres/. It suggests a solution using temporary tables. Can I do something similar, but without temp tables? (I must avoid archive logs). Will creating a temp table create archive logs?

Is there any way to do appending with copy command in a function?

Answer 1

I think you cannot (easily) do that (at least on your PostgreSQL version, see Daniel's answer). What you definitely can, however, is changing your approach, and do a UNION ALL from all those tables in the query part of your COPY. This means there is no looping, but you have to construct your query from the collected table names.

The result would look like

COPY (SELECT * FROM table1
 UNION ALL
 SELECT * FROM table2
 ...) 
 TO tmp/result.txt;

Notes:

• there is no such thing as a psql function. psql is a client to PostgreSQL. It has built in commands (basically everything starting with \), however.
• it is not so clear what you mean by 'archive logs'. If it is the write-ahead log (WAL), then with the above approach you don't have to worry about it. Otherwise, you can use unlogged tables.
• if the tables are big, there is a chance your system will write (a lot of) temp files. This might make the execution not so fast.

• RBB: thanks for point me out. I aware of that psql, but I used to be and didn't realize while typing. Yes, its WAL, i need to avoid that. i will check the table's creation option(s) and will post which suits / solves my scenario.
• RBB: unlogged says, fast than ordinary tables and not crash-safe. Its fine for my scenario. where i can find about the temp files creation. is there any documentation page?

Answer 2

Assuming a Unix system

with PostgreQSL 9.3 or newer

Use the PROGRAM clause with cat as the program.

In the context of a loop in the plpgsql language, where tableName would be a variable, the invocation would typically look like:

execute format(
 'copy (select * from %I) to PROGRAM ''cat >>/tmp/result.txt''',
 tableName);

This would append the results to the end of /tmp/result.txt instead of overwriting the file.

Or if tableName is not a variable but directly the name of an actual table, the simpler following invocation will do:

COPY tableName to PROGRAM 'cat >>/tmp/result.txt';

with older versions of PostgreSQL

A named pipe can be used by a separate process to concatenate results. Example:

$ mkfifo /tmp/pgfifo
$ while true; do cat /tmp/pgfifo >>/tmp/result.txt; done

This last command will block. Let it run until all the results are accumulated, then it can be terminated with ^C or kill.

In SQL, the fifo has to be fed with:

 COPY tableName to '/tmp/pgfifo'

or if the context is plpgsql code where tableName is a variable:

 execute 'COPY ' || quote_ident(tableName) || ' TO ''tmp/pgfifo''';

• RBB: if newer version supports, really its cool. But unfortunately, I'm using 9.1 . As of now, I'm using unlogged option and doing testing.
• Ray: In case anyone else is trying this, on PostgreSQL 15, I had to remove the execute '' part to get it working.
• Daniel Vérité: @Ray: that's because in the context of the question, tableName is dynamic so the code is plpgsql (procedural) to build the sql query and then execute it. I've edited this answer to clarify that.

Answer 3

Here is a Windows alternative to @Daniel Vérité's 9.3 answer

execute 'copy (select * from tableName) to PROGRAM ''findstr "^" >> c:\somePath\result.txt'''

I borrowed this findstr technique from this answer.