I'm trying to run an AWS Lambda which upserts data into a Postgres (RDS) table
Here's my table structure:
| id(integer) | name(character varying) | marks(decimal) |
|---|---|---|
| 1 | derp | 42 |
| 2 | derpina | 45.5 |
My upsert query which runs perfectly on my local pgAdmin console:
INSERT INTO mytable (
id, name,
marks)
VALUES (
unnest(ARRAY[1,4,3]::integer[]),
unnest(ARRAY['derp','frog','doge']::string[]),
unnest(ARRAY[100,50.0,122]::decimal[])
) ON CONFLICT
ON CONSTRAINT pkey
DO UPDATE
SET
marks=excluded.marks;
My code snippet:
const pg = require("pg");
const Pool = pg.Pool;
var query = "INSERT INTO mytable (id, name, marks) VALUES (unnest($1::integer[]),unnest($2::string[]), unnest($3::decimal[])) ON CONFLICT ON CONSTRAINT pkey DO UPDATE SET marks=excluded.marks;"
var row = [];
var ids = [1,4,3];
var names = ['derp','tom','jerry'];
var marks = [100,44.5,31];
row.push(ids);
row.push(names);
row.push(marks);
// more code on starting up pool connection, etc...
client.query(query, row, (err, res) => {
if (err) {
console.log("DB upsert error: ", err.stack);
} else {
console.log("upserted " + res.rowCount + " entries");
}
});
My error:
function unnest(unknown) is not unique
I already tried the solution suggested in pq: function unnest(unknown) is not unique, unnest(unknown) is not unique and unnest(array) returns text instead of the actual type but nothing seems to suggest the solution. Please help!
