pgp.helpers.update
should be able to update enum and timestamp.
I have a table ("myTable") it has following columns
id (varchar)
comparator (type enum named as comparator_type with values ['eq', 'ne', 'leq', 'geq', 'gt', 'lt'])
updatedAt (timestamp)
The entries to update are
entries = [
{
id: "someId",
comparator: "gt",
updatedAt: new Date().toISOString()
}
]
// Using pgp.helpers.update
const updateQuery = pgp.helpers.update(entries, ["?id", "comparator", "updatedAt"], "myTable") + ' WHERE v.id = t.id';
console.log(updateQuery);
// printing it here for reference
/**
* update "myTable" as t set "comparator"=v."comparator","updatedAt"=v."updatedAt" from (values('someId','gt','0.92','2023-02-17T19:46:38.723Z')) as v("id","comparator","updatedAt") WHERE v.id = t.id
**/
It is not updating type enum
and timestamp
. This is following error I'm getting
The following code is being used to run the generated query
await pgDb.any(updateQuery);
The following error I'm getting
{
"name": "error",
"severity": "ERROR",
"code": "42804",
"hint": "You will need to rewrite or cast the expression.",
"position": "112",
"file": "parse_target.c",
"line": "587",
"routine": "transformAssignedExpr",
"query": "<query above>"
}
When I tried to run the same query in Dbever, it starts to give me the following,
ERROR: column "comparator" is of type comparator_type but expression is of type text. Hint: You will need to rewrite or cast the expression.
if I change the "comparator"=v."comparator"
to "comparator"='gt'
, then it given me next error that is
ERROR: column "updatedAt" is of type timestamp without time zone but expression is of type text. Hint: You will need to rewrite or cast the expression.
SO clearly, the query generated by pg-promise is not working.
I used pg.helpers.ColumnSet() to resolve my query. It supports casting and worked like charm.
PS: I would like to know what's the difference between Column and ColumnSet.
entries = [
{
id: "someId",
comparator: "gt",
updatedAt: new Date().toISOString()
}
]
const cs = new pg.helpers.ColumnSet([
"?id",
{
name: "comparator",
cast: "comparator_type"
},
{
name: "updatedAt",
cast: "timestamp"
}
]);
// Using pgp.helpers.update
const updateQuery = pgp.helpers.update(entries, cs, "myTable") + ' WHERE v.id = t.id';