Download CSV via PostgreSQL in Vaadin

I got a Vaadin 14 application, which retrieves data from a PostgreSQL 11 database.
Using Java 8 with 9.4-1202-jdbc42.

I try to achieve a download feature for a table of the database. I created a button with following code snippet. It is working and I can download the example data.

StreamResource resource = new StreamResource("foo.csv", () -> new ByteArrayInputStream("foo,foo2,foo3\nboo,boo2,boo3".getBytes()));
Button button = new Button("Click to download");
FileDownloadWrapper buttonWrapper = new FileDownloadWrapper(resource);
buttonWrapper.wrapComponent(button);
add(buttonWrapper);

But I want to download data from the PostgreSQL database, not some static input. So I do a "COPY ... TO STDOUT"-request using the CopyManager (org.postgresql.copy.CopyManager).

String sql = "SELECT * FROM table";
copyManager.copyOut("COPY (" + sql + ") TO STDOUT WITH (FORMAT CSV, HEADER)", outputStream);

Edit:
Creating the StreamResource works now, but PostgreSQL doesn't like my sql statement.
Currently this creates an empty csv only with the table headers.

CopyOut copiedData = copyManager.copyOut(sqlRequest);
ByteArrayInputStream stream = new ByteArrayInputStream(copiedData.readFromCopy());
return new StreamResource("foo.csv", () -> stream);

PostgreSQL response for the same sql request in PgAdmin:

COPY (SELECT * FROM table) TO STDOUT WITH (FORMAT CSV, HEADER)
ERROR:  can't execute COPY TO: use the copy_to() method instead

If I replace STDOUT with a filename, it works in PgAdmin

COPY (SELECT * FROM table) TO 'D:\test.csv' WITH (FORMAT CSV, HEADER);
COPY 63
Query returned successfully in 881 msec.

Although I understand that using STDOUT doesn't make much sense in the PgAdmin console, it should work via JDBC, right?

What's wrong with my sql statement? I can't find any good information about copy_to() method.

Instead of creating a CSV file directly out of your database query, you could use the OpenCSV library to create a CSV file out of your ResultSet.

However, you don't really need the physical file in your file system. There are even some potentially serious downsides to this - files eat up limited disk space, I/O is expensive and there's a potential for accidentally linking one user's file to a different user. StreamResource accepts any kind of input stream, so it might be a better idea to keep the whole operation in memory, without an intermediate physical file.