Convert a ColumnSet column into geometry with pg-promise

Question

I'm creating a ColumnSet object with pg-promise, according to this:

const cs = new pgp.helpers.ColumnSet([
    {name: 'Id',prop: 'Id'},
    {name: 'Lat',prop: 'Lat'},
    {name: 'Lng',prop: 'Lng'},
    {name: 'CreationDateTime',prop: 'CreationDateTime'},
    {name: 'Topic',prop: 'Topic'},
    {name: 'UserId',prop: 'UserId'},
    {name: 'shape',mod: ':raw',prop: 'shape',def: 'point'},
    {name: 'UserName',prop: 'UserName'},
    {name: 'appName',prop: 'appName'},
    {name: 'appVersion',prop: 'appVersion'}
], {
    table: 'Location'
});

def: 'point' point is method to converting into geometry-- This is a value or how can i run point method and do bind in this column (shape) ?

and write this method for bulk inserting :

async function insertMany(values) {
    try {
        let results = await db.none(pgp.helpers.insert(values, cs));
    } catch (error) {
        console.log(error);
    }
}

for converting lat and lng i wrote this method :

const point = (lat, lng) => ({
    toPostgres: () => pgp.as.format('ST_SetSRID(ST_MakePoint($1, $2), 4326)', [Lag, Lng]),
    rawType: true
});

But I got this error:

TypeError: Values null/undefined cannot be used as raw text

According this page:

Raw-text variables end with :raw or symbol ^, and prevent escaping the text. Such variables are not allowed to be null or undefined, or the method will throw TypeError = Values null/undefined cannot be used as raw text.

When point method is not executed, of course that shape filed is null.

Answer 1

First, you are misusing option prop, which is documented as to be used when the destination property name differs from the column name, which is not your case.

And def, as documented also, represents the value when the property is missing. When the property is there set to null or undefined, the value of def isn't used.

You are trying to override the resulting value, that means you need to use property init.

Another issue - your variables inside point implementation switch cases.

In all, your code should look something like this:

const getPoint = col => {
    const p = col.value;
    // we assume that when not null, the property is an object of {lat, lng},
    // otherwise we will insert NULL.
    return p ? pgp.as.format('ST_SetSRID(ST_MakePoint(${lat}, ${lng}), 4326)', p) : 'NULL';
};

const cs = new pgp.helpers.ColumnSet([
    'Id',
    'Lat',
    'Lng',
    'CreationDateTime',
    'Topic',
    'UserId',
    {name: 'shape', mod: ':raw', init: getPoint},
    'UserName',
    'appName',
    'appVersion',
], {
    table: 'Location'
});

And version that uses Custom Type Formatting would look like this:

const getPoint = col => {
    const p = col.value;
    if(p) {
        return {
            toPostgres: () => pgp.as.format('ST_SetSRID(ST_MakePoint(${lat}, ${lng}), 4326)', p),
            rawType: true
           };
    }
    // otherwise, we return nothing, which will result into NULL automatically
};

const cs = new pgp.helpers.ColumnSet([
    'Id',
    'Lat',
    'Lng',
    'CreationDateTime',
    'Topic',
    'UserId',
    {name: 'shape', init: getPoint},
    'UserName',
    'appName',
    'appVersion',
], {
    table: 'Location'
});