Search code examples
javascriptpg-promise

pg-promise multi-row update with geom and json columns

I love pg-promise but am having difficulty inserting multiple rows into a table that has both geom and json columns. I'm not sure of the syntax.

I want to be able to pass in an array of objects for example:

[{pt_title: 'title1', 
       lat: -42.360061, 
       lng: -71.065567, 
 waypoints:[[42.360061,-71.065567],[42.360061,-71.06549]]},
{pt_title: 'title2', 
       lat: -43.360061, 
       lng: -71.065567, 
 waypoints:[[43.360061,-71.065567],[42.360061,-71.06549]]}]

and update my table points:

pt_title varchar,
pt_geom geom,
waypoints json

I therefore have to convert the lat and lng values like so:

ST_GeomFromText(POINT(${pt.lng} ${pt.lat}), 4326)

and also convert the waypoints array into json before updating.

CAST(pt.waypoints AS json)

I read about using the update helper, but couldn't find any examples of gis columns. I can't figure how to do the column set so that it knows about the conversion?

Also, is there a helper utility so I can see the resulting SQL string when using db.none(sql, values) syntax? I'd like to see what the final SQL is with the values inserted so I can see what's going on.

Solution

  • I can't figure how to do the column set so that it knows about the conversion?

    Property init in each Column is your conversion callback.

    Here's your full ColumnSet object:

    const cs = new pgp.helpers.ColumnSet([
    'pt_title',
    {
        name: 'pt_geom',
        mod: ':raw',
        init: c => pgp.as.format('ST_GeomFromText(POINT(${lng}, ${lat}), 4326)', c.source)
    },
    'waypoints:json'
], {table: 'my-table'});

    Also, is there a helper utility so I can see the resulting SQL string...?

    See pg-monitor, or just handle event query.