I have a project where I need to do a update multiple rows at once. I have found the example on how to do this is the docs: documentation
I have done used a columnset because it is being recommended to do to in the documentations. I have set the ?feature_id so it is only used in the WHERE clause.
The error my code is generating is the following: error: column "created_on" is of type timestamp with time zone but expression is of type text. I have noticed in the query that is being generated and that seems to be in line with the example.
This code has an insert statement for the features that are new and that seems to work fine. The error is only being thrown on the update query.
const insertValues = [];
const updateValues = [];
for (let i = 0; i < features.length; i += 1) {
const feature = features[i];
if (!excistingFeaturesIds.includes(feature.id)) {
insertValues.push({
plot_id: plotId,
type: feature.type,
area: feature.area,
created_on: currendDate,
updated_on: currendDate,
geo_feature: feature.geoFeature,
});
} else {
updateValues.push({
feature_id: feature.id,
plot_id: plotId,
type: feature.type,
area: feature.area,
created_on: currendDate,
updated_on: currendDate,
geo_feature: feature.geoFeature,
});
}
}
const insertColumnSet = new pgp.helpers.ColumnSet(['plot_id', 'type', 'area', 'created_on', 'updated_on', 'geo_feature'], { table: 'features' });
const updateColumnSet = new pgp.helpers.ColumnSet(['?feature_id', 'plot_id', 'type', 'area', 'created_on', 'updated_on', 'geo_feature'], { table: 'features' });
if (insertValues && insertValues.length > 0) {
const insertQuery = pgp.helpers.insert(
insertValues, insertColumnSet,
);
await promiseDB.none(insertQuery);
}
if (updateValues && updateValues.length > 0) {
const updateQuery = `${pgp.helpers.update(
updateValues, updateColumnSet,
)} WHERE v.feature_id = t.feature_id`;
console.log(updateQuery);
await promiseDB.none(updateQuery);
}
return res.status(201).json({
message: 'Features added!',
});
} catch (err) {
console.log(err);
return res.status(400).send(err);
}
UPDATE
"features" AS t
SET
"plot_id" = v. "plot_id",
"type" = v. "type",
"area" = v. "area",
"created_on" = v. "created_on",
"updated_on" = v. "updated_on",
"geo_feature" = v. "geo_feature"
FROM (
values(1, 3, 'roof', 342.01520314642977, '2021-07-20T09:56:10.007+02:00', '2021-07-20T09:56:10.007+02:00', '{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[coords...]]]},"properties":{"UIDN":6338864,"OIDN":5290477,"VERSIE":1,"BEGINDATUM":"2015-09-23","VERSDATUM":"2015-09-23","TYPE":1,"LBLTYPE":"hoofdgebouw","OPNDATUM":"2015-08-25","BGNINV":5,"LBLBGNINV":"kadastralisatie","type":"roof","tools":"polygon","description":"D1","id":5290477,"area":342.01520314642977,"roofType":"saddle","roofGreen":"normal","database":true},"id":1}'),
(2,
3,
'roof',
181.00725895629216,
'2021-07-20T09:56:10.007+02:00',
'2021-07-20T09:56:10.007+02:00',
'{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":6338518,"OIDN":5290131,"VERSIE":1,"BEGINDATUM":"2015-09-23","VERSDATUM":"2015-09-23","TYPE":1,"LBLTYPE":"hoofdgebouw","OPNDATUM":"2015-08-25","BGNINV":5,"LBLBGNINV":"kadastralisatie","type":"roof","tools":"polygon","description":"D2","id":5290131,"area":181.00725895629216,"roofType":"flat","roofGreen":"normal","database":true},"id":2}'),
(3,
3,
'roof',
24.450163203958745,
'2021-07-20T09:56:10.007+02:00',
'2021-07-20T09:56:10.007+02:00',
'{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":5473377,"OIDN":4708120,"VERSIE":1,"BEGINDATUM":"2014-07-04","VERSDATUM":"2014-07-04","TYPE":2,"LBLTYPE":"bijgebouw","OPNDATUM":"2014-05-27","BGNINV":4,"LBLBGNINV":"bijhouding binnengebieden","type":"roof","tools":"polygon","description":"D3","id":4708120,"area":24.450163203958745,"roofType":"saddle","roofGreen":"normal","database":true},"id":3}'),
(4,
3,
'water',
57.65676046589426,
'2021-07-20T09:56:10.007+02:00',
'2021-07-20T09:56:10.007+02:00',
'{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":473256,"OIDN":199890,"VERSIE":2,"BEGINDATUM":"2017-03-08","VERSDATUM":"2021-05-06","VHAG":-9,"NAAM":"nvt","OPNDATUM":"2017-01-30","BGNINV":4,"LBLBGNINV":"bijhouding binnengebieden","type":"water","tools":"polygon","description":"W1","id":199890,"area":57.65676046589426,"waterType":"natural","database":true},"id":4}'))
AS v ("feature_id",
"plot_id",
"type",
"area",
"created_on",
"updated_on",
"geo_feature")
WHERE
v.feature_id = t.feature_id
INSERT INTO "features" ("plot_id", "type", "area", "created_on", "updated_on", "geo_feature")
values(3, 'roof', 342.01520314642977, '2021-07-20T10:17:04.565+02:00', '2021-07-20T10:17:04.565+02:00', '{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":6338864,"OIDN":5290477,"VERSIE":1,"BEGINDATUM":"2015-09-23","VERSDATUM":"2015-09-23","TYPE":1,"LBLTYPE":"hoofdgebouw","OPNDATUM":"2015-08-25","BGNINV":5,"LBLBGNINV":"kadastralisatie","type":"roof","tools":"polygon","description":"D1","id":5290477,"area":342.01520314642977,"roofType":"saddle","roofGreen":"normal","database":true},"id":1}'), (3, 'roof', 181.00725895629216, '2021-07-20T10:17:04.565+02:00', '2021-07-20T10:17:04.565+02:00', '{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":6338518,"OIDN":5290131,"VERSIE":1,"BEGINDATUM":"2015-09-23","VERSDATUM":"2015-09-23","TYPE":1,"LBLTYPE":"hoofdgebouw","OPNDATUM":"2015-08-25","BGNINV":5,"LBLBGNINV":"kadastralisatie","type":"roof","tools":"polygon","description":"D2","id":5290131,"area":181.00725895629216,"roofType":"flat","roofGreen":"normal","database":true},"id":2}'), (3, 'roof', 24.450163203958745, '2021-07-20T10:17:04.565+02:00', '2021-07-20T10:17:04.565+02:00', '{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":5473377,"OIDN":4708120,"VERSIE":1,"BEGINDATUM":"2014-07-04","VERSDATUM":"2014-07-04","TYPE":2,"LBLTYPE":"bijgebouw","OPNDATUM":"2014-05-27","BGNINV":4,"LBLBGNINV":"bijhouding binnengebieden","type":"roof","tools":"polygon","description":"D3","id":4708120,"area":24.450163203958745,"roofType":"saddle","roofGreen":"normal","database":true},"id":3}'), (3, 'water', 57.65676046589426, '2021-07-20T10:17:04.565+02:00', '2021-07-20T10:17:04.565+02:00', '{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[coords...]]]},"properties":{"UIDN":473256,"OIDN":199890,"VERSIE":2,"BEGINDATUM":"2017-03-08","VERSDATUM":"2021-05-06","VHAG":-9,"NAAM":"nvt","OPNDATUM":"2017-01-30","BGNINV":4,"LBLBGNINV":"bijhouding binnengebieden","type":"water","tools":"polygon","description":"W1","id":199890,"area":57.65676046589426,"waterType":"natural","database":true},"id":4}')
Your columns created_on and updated_on need SQL type casting, hence the error.
And there is no need re-creting the same list of table -> columns.
In all, your column-sets can be created like this:
const insertColumnSet = new pgp.helpers.ColumnSet([
'plot_id',
'type',
'area',
{name: 'created_on', cast: 'timestamptz'},
{name: 'updated_on', cast: 'timestamptz'},
'geo_feature'
], { table: 'features' });
const updateColumnSet = insertColumnSet.extend(['?feature_id']};
See Column full syntax, plus extend method.
Note that strictly speaking, only the UPDATE needs the type casting, while your INSERT can infer the type automatically, which you can reflect in the column-sets like this:
const insertColumnSet = new pgp.helpers.ColumnSet(['plot_id', 'type', 'area',
'created_on', 'updated_on', 'geo_feature'], { table: 'features' });
const updateColumnSet = insertColumnSet.merge([
{name: 'feature_id', cnd: true}, // or just '?feature_id'
{name: 'created_on', cast: 'timestamptz'},
{name: 'updated_on', cast: 'timestamptz'}
]};
Both approaches will work fine in your case though ;)
See merge method.