Using placeholders with copy and pg-query-stream

Question

I'm trying to extract a query as a csv file. I tried to use copy with pg-query-stream to execute the query but I'm facing this error:

error: bind message supplies 1 parameters, but prepared statement "" requires 0

when removing copy from the query it works fine with the placeholders also if I provided a query with copy and no placeholders it works fine.

const pgp = require('pg-promise')
const QueryStream = require('pg-query-stream')
query1 = "copy (select * from real_state WHERE town_code= $1 ) TO  '/tmp/file.csv'"
const qs = new QueryStream(query1, [22])
await db.stream(qs, s => {
            // initiate streaming into the console:
            s.pipe(JSONStream.stringify()).pipe(process.stdout)
        }).then(data => {
        }).catch(error => {
            console.log('ERROR:', error)
        })

---

query1 = "copy (select * from real_state WHERE town_code= $1 ) TO  '/tmp/file.csv'" ==> error
query2 = "copy (select * from real_state) TO  '/tmp/file.csv'" ==> It works
query3 = "select * from real_state WHERE town_code= $1" ==>  It works

Answer 1

There is a limitation there within the COPY context, which prohibits you from using any parameters.

But you can work-around that limitation, using pg-promise query formatting:

const query = pgp.as.format('COPY(SELECT * FROM real_state WHERE town_code = $1) TO $2',
                            [22, '/tmp/file.csv']);