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
Advertisement
Answer
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']);