Execute multiple queries in a single statement using postgres and node js

Question

I need to execute insert and delete query in a single statement like this

INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');DELETE FROM  COMPANY WHERE ID='12';

This is my node.js code for executing query

pg.connect(pgConString, function (err, client, done) {
        if (err) {
            callBack("DB connection failed. " + err, null);
            return;
        }
        var query = client.query({
            text: "INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');DELETE FROM  COMPANY WHERE ID='12';"
            values: [1, "Poul1"],
            name: "insertQuery"
        });
        query.on("error", function (err) {
            callBack("DB insertion failed. Error Message: " + err, null);
            return;
        });

        query.on('end', function (result) {

            done();
            return;
        });
    });

I got error message like this

error: cannot insert multiple commands into a prepared statement

is it possible to execute multiple queries in postgresql database using node.js ?

You might want to look into using a Postgres FUNCTION. postgresqltutorial.com/postgresql-create-function — Luke
– Luke, Commented Dec 17, 2019 at 23:28

n3ko · Accepted Answer · 2021-08-09 17:11:31Z

10

Although there is an accepted answer, it's a bit obsolete. For now node-postgres handles multiple queries in one call and returns a neat little array to you, like:

const db.query('select 1; select 2; select 3;')
results.map(r => (r.rows[0]['?column?']))
// [ 1, 2, 3 ]

There is also an alternative 'opinionated' library, called pg-promise, which also accepts query chains in one call and works with sql files as well.

edited Aug 9, 2021 at 17:11

answered Aug 8, 2021 at 18:10

n3ko

4154 silver badges10 bronze badges

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

4 Comments

vitaly-t Over a year ago

Because of your answer, I added one for pg-promise also :)

Jahir Over a year ago

Note that the question uses parameters during the query. Would it also work with parameters without building the query first using something like pg-format

Oliver Bock Over a year ago

No, if you use parameters then you'll get an error cannot insert multiple commands into a prepared statement

n3ko Over a year ago

True. Also mentioned in @Jahir's earlier comment. Sendin' more statements at once AND using parameters probably means, that you should turn to user-defined functions or procedures. You could also create a single string from your statement chain with the parameters inserted (as suggested in other answers and comments), but there is a performance price, cause pg has to parse and plan again and again all the queries before every subsequent runs. (If they are separate parametrized queries or you have a backend function for them, it can plan just once and cache the plans for later)

vitaly-t · Accepted Answer · 2021-08-08 22:09:32Z

5

When using pg-promise...

First, we declare our queries + values via a flexible QueryFormat list:

const queries = [
    {query: 'select * from products where price > $1', values: [12.5]},
    {query: 'select * from payments where amount < ${amount}', values: {amount}}
];

Then we create a single-query formatted string:

const sql = pgp.helpers.concat(queries);

And then we execute it, and retrieve the result:

const [products, payments] = await db.multi(sql);

See: concat, multi.

answered Aug 8, 2021 at 22:09

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Comments

Juha Palomäki · Accepted Answer · 2017-06-04 18:15:09Z

4

Try like this

pg.connect(pgConString, function (err, client, done) {
    if (err) {
        callBack("DB connection failed. " + err, null);
        return;
    }
    client.query({
        text: "INSERT INTO COMPANY (ID,NAME) VALUES (1, 'Paul');",
        values: [1, "Poul1"],
        name: "insertQuery"
    });

    client.query({
        text: "DELETE FROM  COMPANY WHERE ID='12';",
        name: "deleteQuery"
    });

    client.on("error", function (err) {
        callBack("DB insertion failed. Error Message: " + err, null);
        return;
    });


});

edited Jun 4, 2017 at 18:15

Juha Palomäki

27.1k2 gold badges41 silver badges44 bronze badges

answered Feb 25, 2016 at 12:29

anas p a

4096 silver badges22 bronze badges

2 Comments

DILP Over a year ago

Be Warned: Because each query above is an asynchronous operation, you cannot count on them finishing in the order as written. If the flow doesn't need to be controlled, then it's fine. But if it does, control the flow with callbacks, promises or async/await. Examples in the node-postgres docs

Bergi Over a year ago

@DILP Given that queries on the same client (onnection) are queued by node-postgres, the order is actually guaranteed. But you'd still want to wait for them sequentially, that's true.

Collectives™ on Stack Overflow

Execute multiple queries in a single statement using postgres and node js

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related