Multi user connection in node-postgres/pg-promise

I wish to have connected more than 1 users at the same time, as follows:// in node-postgresconst { Pool } = require('pg');const pool_1 = new Pool({ user: 'dbuser_1', host: 'database.server.com', database: 'mydb', password: 'secretpassword_1', port: 3211,});const pool_2 = new Pool({ user: 'dbuser_2', host: 'database.server.com', database: 'mydb', password: 'secretpassword_2', port: 3211,});// in pg-promiseconst initOptions = { ... };const pgp = require('pg-promise')(initOptions);const cn_1 = 'postgres://username_1:p...Read more

pg promise - stream: how to get row as array (instead of JSON object)

We need to export data to a csv file.I think a nice approach is to use the streaming feature of pg-promise Learn-by-Example#from-databaseIt seems that the stream reads the data from the db and transforms the row-data to a JSON object. Then we need to convert the JSON object back to an array and do the csv export.Is there a way to configure the streaming feature to directly return the rows as array to improve performance (and avoid the useless JSON conversion)?i.e. something like ParameterizedQuery.rowMode...Read more

pg-promise: when are BEGIN and COMMIT sent?

This question is for the purpose of latency optimization.In pg-promise, when/how are the BEGIN and COMMIT commands sent when using transactions?For example, if there is the code:db.tx(t => { const query1 = t.none(...); const query2 = t.none(...);})Will the BEGIN command be sent together with the first query or before, in a separate packet? Similarly, will the COMMIT command be sent together with the second query or after?In other words, will this transaction take 2, 3, or 4 round trips?...Read more

How to call pg-promise helpers.insert function?

What is the best way to provide 'pgp' to the following function?Considering 'db' can be db connection, transaction (tx), or task from the above.module.exports = async (db, tableName, records) => { const record_ = records[0] const columns_ = [] for (const c_ in record_) { columns_.push(c_) } const insert_ = pgp.helpers.insert(records, columns_, tableName) return db.result(insert_, null, r => r.rowCount)}...Read more

How do I write queries with a null constraint in pg-promise properly?

When writing Postgres queries, constraints are usually written like WHERE a = $(a) or WHERE b IN $(b:csv) if you know it's a list. However, if a value is null, the constraint would have to be written WHERE x IS NULL. Is it possible to get the query to auto-format if the value is null or not?Say I might want to find rows WHERE c = 1. If I know c is 1, I write the query like db.oneOrNone(`SELECT * FROM blah WHERE c = $(c), { c })But if c turns out to be null, the query would have to become ...WHERE c IS NULL. Would it be possible to construct a g...Read more

Massive Insert from a JSON file using Pg-Promise

Load huge JSON file using Pg-Promise helpers and fs stream.I'm using pg-promise and I want to make massive inserts into a table using pgp.helpers. I've seen solution like Multi-row insert with pg-promise and also followed Data import for streams (Spex) but still it fails with the same error as in this post https://github.com/vitaly-t/spex/issues/8I tried using a example from the other post on CSV stream(rs.csv()) but when i replaced the same with JSonStream parser I still get the same error. Can you please share a working example? db.tx(t =>...Read more

Module export of pg-promise object derived from promise chain

We're using HashiCorp's Vault to store database connection credentials, then constructing the connection string for pg-promise from those. The 'catch' is that the Vault details are provided from a Promise wrapper, due to request callbacks to the Vault API.Example database.js module:const pgp = require('pg-promise')(/* options obj */);const getDbo = () => { return new Promise( (resolve, reject) => { vault.init().then(secrets => { let credentials = secrets.dbUser + ':' + secrets.dbPass let connStr = 'postgres://' ...Read more

Does pg promise support WITH clause in query file?

If I put this in one query file how will pg promise handle it?WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) )SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;Is there a need to use WITH at...Read more

pg-promise: support polymorphic types

I have the following polymorphic function:CREATE OR REPLACE FUNCTION check_value_existence( p_value anynonarray, p_column_name information_schema.sql_identifier, p_table_name name, p_schema_name information_schema.sql_identifier DEFAULT 'public'::character varying)RETURNS boolean AS$BODY$...$BODY$LANGUAGE plpgsqland I am calling it as follows: db.func("check_value_existence", [account.username, "username", "account", "customer"]) .then(found => { ...}) .catch(error => {...});but I get the following error:could not determine po...Read more

Same name attributes in select list in pg-promise

Is it possible to get the same name attributes in the select list (as JSON deduplicates them)?For instance:CREATE TABLE t1 ( id int;);INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);CREATE TABLE t2 ( id int;);INSERT INTO t2 VALUES(1);SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.idshould return:id id -----1 12 nullbut will return instead:id---1nullI'm trying to build a web-based SQL editor, and this is kind of a showstopper....Read more

pg-promise hangs after six queries

I'm working on a project using pg-promise. I've tried querying a few different ways with pg-promise but they all seem to cause it to hang after 6 queries. It seems to me like the connections aren't being closed but I can't find anything in the documentation about closing a connection after a query.Here's what I havevar cn = {host: 'localhost',port: 5432,database: 'db',user: 'user',password: 'password'};var db = pgp(cn);function query(sql, params) {return db.task(function (t) { // this = t = task protocol context; // this.ctx = task config...Read more

Insert with id of type serial in pg-promise

In pg-promise, how can I insert data when the primary key is of type Serial? Omitting the field id creates no response in the call.The code below produces no error in the catch (and also does not execute the then branch).function postSecao(req, res){ var data = req.body; var db = pgp(cn); db.none("insert into public.secoes(nome) values($1)", [data.nome]) .then(function () { pgp.end(); return res.status(201).end(); }) .catch(function (error) { console.log(err); pgp.end(); return res.status(500).end(); });}The tabl...Read more

Node pg-promise, bind multiple values with type casting

I'm currently using the pg-promise library to insert multiple values into a database in the format:const cs = new pgp.helpers.ColumnSet(['booking_id', {name:'timeslot', cast:'timestamp'}], {table: 'booking'});// data input values:const values = [];bookings.forEach(slot => { values.push({booking_id: booking_id, timeslot: slot});});Where I need timeslot to be a timestamp. However it comes into the API as value like 1515586500.0Using the above cast property my query gets resolved like so insert into "booking"("booking_id","timeslot") values(1...Read more