CODEWITHSUNDEEP
javascriptsqlnode.jspostgresql
user1575921
user1575921

Reputation: 1088

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

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

Upvotes: 1

Views: 4721

Answers (4)

vitaly-t
vitaly-t

Reputation: 25940

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;
    });

Upvotes: 1

Fka
Fka

Reputation: 6234

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.

Upvotes: 2

Arjan
Arjan

Reputation: 9884

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));
    });
  });
});

Upvotes: 2

FlyingGuy
FlyingGuy

Reputation: 333

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.

Upvotes: -1

Related Questions