How to insert in two tables in one time? prepared statements

Question

How to insert two table in one time?
I need to insert second table user_information the field user_id with first table user insert returning id, I found this answer but I can't find how to be with params prepared statements

var dbQuery = 'WITH insertUser AS (
  INSERT INTO "user" (status, create_date) VALUES ($1, $2) RETURNING id
  )
  , insertUserInformation AS (
  INSERT INTO user_information (user_id, email) VALUES ($3, $4)
  )
';

yield queryPromise(dbClient, dbQuery, [status, timestamp, ??, email]);

pg

Like this: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#transactions — vitaly-t, Mar 25 '16 at 19:21
Did any of the answers help you? If so, please accept that one that did. — vitaly-t, Apr 07 '16 at 22:24

Fka · Answer 1 · 2016-03-25T19:42:26.363

2

It's impossible in postgresql. I solved exact the same problem by creating function and simply executing with parameters. As I see in your table structure, you don't have many attributes, so this will be relatively easy.

Example code:

function.sql

CREATE OR REPLACE FUNCTION createSomething
(
    IN attr1 VARCHAR(20),
    IN attr2 VARCHAR(200)
)
RETURNS void AS $$
DECLARE userId INTEGER;
BEGIN
    INSERT INTO table1 (col1, col2) VALUES
    (
        attr1,
        attr2
    ) RETURNING id INTO userId;

    INSERT INTO table2 (user_id, col11, col2) VALUES
    (
        userId,
        col11,
        col12
    );
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT createSomething('value1', 'value2');

Please notice, that second insert statement will know what was recently user's id and will use it.

edited Mar 25 '16 at 19:42

answered Mar 25 '16 at 19:06

Fka

6,044
5
42
60

thanks for reply, would you show me some example how to do this? – user1575921 Mar 25 '16 at 19:08
I edited answer. I'm not familiar with `node-postgres` so you have to connect both technologies on your own. – Fka Mar 25 '16 at 19:28
Just use transactions. If the second query fails, you can do a rollback and the database will still be in a consistent state, almost as if nothing happened. – Arjan Mar 25 '16 at 19:35
By default, all functions in postgresql runs inside transaction scope so it doesn't matter if you have or not transaction inside client code. – Fka Mar 25 '16 at 19:37
use transaction or query like @Fka provide , any difference performance or other issue? – user1575921 Mar 25 '16 at 20:51
It is very possible in PostgreSQL, see [Inserting multiple records with pg-promise](http://stackoverflow.com/questions/36233566/inserting-multiple-records-with-pg-promise), but it is impossible with Prepared Statements. – vitaly-t Mar 26 '16 at 11:50

score 2 · Accepted Answer · answered Mar 25 '16 at 19:12

Use transactions. That way either all queries will be committed, or none will be committed. And the incomplete state before you have executed all queries is not visible for other processes.

More on how to do transactions in node-postgres is available at https://github.com/brianc/node-postgres/wiki/Transactions

And for reference the most relevant section is:

var Client = require('pg').Client;

var client = new Client(/*your connection info goes here*/);
client.connect();

var rollback = function(client) {
  //terminating a client connection will
  //automatically rollback any uncommitted transactions
  //so while it's not technically mandatory to call
  //ROLLBACK it is cleaner and more correct
  client.query('ROLLBACK', function() {
    client.end();
  });
};

client.query('BEGIN', function(err, result) {
  if(err) return rollback(client);
  client.query('INSERT INTO account(money) VALUES(100) WHERE id = $1', [1], function(err, result) {
    if(err) return rollback(client);
    client.query('INSERT INTO account(money) VALUES(-100) WHERE id = $1', [2], function(err, result) {
      if(err) return rollback(client);
      //disconnect after successful commit
      client.query('COMMIT', client.end.bind(client));
    });
  });
});

Does this work asynchronously? If 10,000 instances of a function performing this code are called at the same time, is it guaranteed that the postgres driver won't receive invalid statements? (E.g. a "BEGIN" statement following immediately by another "BEGIN") — Gershom Maes, Jan 08 '18 at 19:06

vitaly-t · Answer 3 · 2019-07-28T10:38:21.420

PostgreSQL Prepared Statements will not let you do it. You will have to use a transaction.

Below is your example implemented with pg-promise, using ES7 syntax:

const pgp = require('pg-promise')({
    // initialization options;
});

const db = pgp(/* your connection object or string */);

db.tx(async t => {
        const user = await t.one('INSERT INTO user(status, create_date) VALUES($1, $2) RETURNING id', [status, timestamp]);
        return t.none('INSERT INTO user_information(user_id, email) VALUES($1, $2)', [user.id, email]);
    })
    .then(() => {
        // SUCCESS;
    })
    .catch(error => {
        // ERROR;
    });

score -1 · Answer 4 · answered Mar 25 '16 at 19:02

-1

I do not believe this can be accomplished as a natural sql statement. You have to wrap it up as a procedure or some other mechanism.

answered Mar 25 '16 at 19:02

FlyingGuy

333
1
9

1

You absolutely CAN do this in a single SQL statement in Postgres, as the post shows. Maybe you can't do it with bound parameters, but otherwise it's certainly possible. – Jim Nasby Mar 25 '16 at 19:36
Sorry, but the post shows that two distinct sql statements are used. I am not saying you are incorrect in your method, but I am stating that it cannot be accomplished in a SINGLE NATURAL SQL Statement. – FlyingGuy Mar 25 '16 at 20:54

How to insert in two tables in one time? prepared statements

4 Answers4