0

I have an insert on postgres 10 that uses subqueries to look up foreign keys on a unique column, like this (just an illustration example):

CREATE TABLE company 
(id SERIAL PRIMARY KEY, 
name VARCHAR(50) NOT NULL,
companyCode CHAR(4) NOT NULL UNIQUE);

CREATE TABLE customer 
(id SERIAL PRIMARY KEY,
company NOT NULL REFERENCES company (id),
name VARCHAR(100) NOT NULL);

INSERT INTO customer (name, company) values ('Bill Gates', (select id from company where companyCode='MSFT'));

I'm trying to use the pg-promise NodeJS library to insert multiple rows into the database quickly. The recommended way seems to be to use helpers. However I haven't found anything in the documentation or the examples that refers to using subqueries in this way.

Am I missing an obvious way to do this? Or am I approaching the whole question in the wrong way, and is there a simpler way than pg-promise to generate the bulk insert code while still keeping the injection protection?

Thanks!

George Gull
  • 1
  • 1
  • 1
    Why can't you just put into external SQL file with variables, and set variables for the last query, like - `INSERT INTO customer (name, company) values (${name}, (select id from company where companyCode=${company}));`? See [Query Files](https://github.com/vitaly-t/pg-promise#query-files). – vitaly-t Apr 01 '18 at 18:40
  • Are you going to follow up or do you want to close it at this point? – vitaly-t Apr 19 '18 at 14:06
  • @vitaly-t I think he (and now, I) was looking for how to use the `helpers.insert` function with subqueries. i.e. can you take data like `[{a: 1}, {a: 2}]` and generate an insert query like `INSERT INTO table (fk_id) VALUES ((SELECT id FROM table2 WHERE a=1), (SELECT id FROM table2 WHERE a=2))` etc.. – BrDaHa Aug 03 '19 at 00:27
  • @BrDaHa You wouldn't use `helpers.insert` for that, it is not a multi-row insert from values, it is a regular query you run yourself. – vitaly-t Aug 03 '19 at 07:29
  • @vitaly-t What if, instead of one customer value, I would like to insert a list of customers? Wouldn't it be a multi-row insert? Would it be possible to run the query above with subquery? – fa__ May 02 '23 at 14:10
  • @fa__ Multi-row inserts in `pg-promise` are for inserting multi-row data from memory, while data in sub-queries resides inside the server. Anything else wouldn't be relevant to `pg-promise` specifically. – vitaly-t May 02 '23 at 14:16

0 Answers0