Insert objects without matching column name

Question

This is probably a silly question, but I am new to node and postgresql, so I am struggling.

I am trying to use pgp.helpers.insert to insert multiple objects into the database, as the example bellow:

users = [{mycolumn:{name: 'John', age:23}}, {mycolumn:{name: 'Mike', age: 30}}];
query = pgp.helpers.insert(users, ['mycolumn:json'], 'mytable');
// insert into "mytable"("mycolumn") values('{"name":"John","age":23}'),('{"name":"Mike","age":30}')

The code above inserts into mytable 2 rows with mycolumn as a jsonb.

But I am trying to insert straight into mycolumn the values inside an array of objects, without wrapping my original object, such as:

users = [{name: 'John', age:23}, {name: 'Mike', age: 30}];
query = pgp.helpers.insert(users, ['mycolumn:json'], 'mytable');
// Error: Property 'mycolumn' doesn't exist.

Of course it doesn't work, since the object doesn't contain a mycolumn attribute. But I think it is not elegant to iterate in my array wrapping the original object with the column name, specially since I am working with millions of rows (working in batches, of course).

Thanks in advance.

Answer 1

You can use the following definition for your column, as per the Column syntax:

{
  name: 'mycolumn',
  init: c => c.source // use the source object itself
}

i.e.

const cs = new pgp.helpers.ColumnSet([
    {
      name: 'mycolumn',
      init: c => c.source
    }
], {table: 'mytable'});

and then use it:

const query = pgp.helpers.insert(users, cs);

Note that we are not specifying the modifier - mod: ':json', because we are returning an object from init, and objects are formatted as JSON by default.