CODEWITHSUNDEEP
javascriptnode.jspostgresqlpg-promise
Mostafa Labib
Mostafa Labib

Reputation: 809

Using placeholders with copy and pg-query-stream

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

Upvotes: 1

Views: 1623

Answers (2)

vitaly-t
vitaly-t

Reputation: 25840

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

Upvotes: 1

Bergur
Bergur

Reputation: 4057

I think you're mixing together two features.

pg-query-stream returns the rows as stream, there's no need to use copy in the select there. Just use a simple select, then pipe the results to a filestream.

const fs = require('fs')
const { Pool } = require('pg')
const QueryStream = require('pg-query-stream')

const query = new QueryStream("select * from real_state WHERE town_code= $1", [22]
const stream = pool.query(query)
const fileStream = fs.createReadStream('/tmp/file.csv')
fileStream.pipe(stream)

If you want to use copy then use pg-copy-streams: https://github.com/brianc/node-pg-copy-streams

const fs = require('fs')
const { Pool } = require('pg')
const copyFrom = require('pg-copy-streams').from
const stream = db.query(copyFrom('COPY real_state FROM stdin WHERE town_code= $1', [22])
const fileStream = fs.createReadStream('/tmp/file.csv')

fileStream.pipe(stream)

Upvotes: 0

Related Questions