How do I send in parameters in COPY TO-query in node-postgres

Question

Im doing a query to export results to CSV in postgres running on a ubuntu-server.

The query gets called from a Node-API which is connecting to Postgres via the node-postgres-driver(https://node-postgres.com/).

The query looks like this:

var sqlText = 
   'Copy (SELECT company.name AS Company, SUM(sale.numbersold) AS NumberSold 
    FROM SALE LEFT JOIN company ON company.companyid = sale.companyid 
    WHERE sale.createdate BETWEEN $1 AND $2 GROUP BY company.name) 
    To \'/tmp/test.csv\' With CSV HEADER DELIMITER \',\'';

Whenever I try to use this query with parameters I get a exception that postgres expected 0 parameters and got 2. How do you send in parameters to a query like this?

If I query with static text like this, it works:

var sqlText = 'Copy (SELECT company.name AS Company, SUM(sale.numbersold) AS NumberSold 
FROM SALE LEFT JOIN company ON company.companyid = sale.companyid 
WHERE sale.createdate BETWEEN \'2018-01-01\' AND \'2018-01-01\' 
GROUP BY company.name) 
To \'/tmp/test.csv\' With CSV HEADER DELIMITER \',\'';

Code in Node:

var params = ['2018-01-01', '2018-01-28']

 let dbResult;
            try {
                    dbResult = await GetStuff(sqlText, params);
            } catch (err) {

            }

Daniel Vérité · Accepted Answer · 2018-03-01 11:42:03Z

1

COPY is an utility statement, and as such doesn't support parametrized execution.

Either the parameters should be injected client-side, or, if you prefer not to, injected server-side with dynamic SQL implemented in a function.

An example is shown in Use function variable in dynamic COPY statement.

answered Mar 1, 2018 at 11:42

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5283666 Over a year ago

Ok, I feared that was the case. I will try to inject it clientside. Thank you for your answer!

Collectives™ on Stack Overflow

How do I send in parameters in COPY TO-query in node-postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related