to_json function in Postgres works great from command prompt. Get a type error executing same query in node-pg

Question

PostgreSQL database has a table for companies and one for invoices. Desire to query invoices and representing company as an object under a pseudo column called company: So this query:

SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) "company" FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = 1;

when executed at the command prompt in psql yields the expected result:

id | amt | paid | add_date | paid_date | company
----+-----+------+------------+-----------+--------------

1 | 100 | f | 2024-01-04 | | {"code":"apple","name":"Apple Computer","description":"Maker of OSX."}

When I execute the same query using Node-pg I get a type error saying that type to_json does not exist.

In my node-js app, I execute the following:

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) 'company' FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1;", [id]);

The result is run through res.json and the result:

{
    "error": {
        "length": 94,
        "name": "error",
        "severity": "ERROR",
        "code": "42704",
        "position": "54",
        "file": "parse_type.c",
        "line": "270",
        "routine": "typenameType"
    },
    "message": "type \"to_json\" does not exist"
}
  

Answer 1

I think I may have solved it. The way I had the query was originally like this:

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) 'company' FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1", [id]);

I changed it to this:

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) "company" FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1", [id]);

Note the way I did the quotes around the word "company" after the to_json call. That was just a random guess on my part, and it worked.

Now my results (from Insomnia):

{ "invoice": { "id": 2, "amt": 200, "paid": false, "add_date": "2024-01-04T05:00:00.000Z", "paid_date": null, "company": { "code": "apple", "name": "Apple Computer", "description": "Maker of OSX." } } }