javascriptnode.jspg-promise

Is it possible to enum and timestamp using pgp.helpers.update?


Expected behavior

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
**/

Actual behavior

It is not updating type enum and timestamp. This is following error I'm getting

Steps to reproduce

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.

Environment

Link of gihub issue I created - https://github.com/vitaly-t/pg-promise/issues/866

Solution

  • 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';