'knex with pg_column_size in the returning function

  • Knex: 0.95.12
  • Database: PostgreSQL 11.7
  • OS: Debian GNU/Linux 9 (stretch) (official Docker image)
  • Node: v12.22.6

Is it possible for knex to return the results of a call to pg_column_size via the returning function call?

This works in native SQL:

create table "MyTable"
(
    "ID"       serial
        constraint "MyTable_pkey"
            primary key,
    "MyColumn" varchar
);

INSERT INTO "MyTable" ("MyColumn")
VALUES ('Whatever')
RETURNING "ID", pg_column_size("MyColumn");

Result:

+--+--------------+
|ID|pg_column_size|
+--+--------------+
|4 |9             |
+--+--------------+

But I suppose I'm not surprised that knex expects an actual column to return when doing ...

.returning(["ID", 'pg_column_size("MyColumn")'])

Result:

ERROR:  column "pg_column_size("MyColumn")" does not exist ...

I am hoping for a way to return that data without resorting to a subsequent select query after the insert.

postgresqlknex.js


Solution 1:[1]

A fix is to use knex.raw() inside the returning array like this:

.returning(["ID", source.raw('pg_column_size("MyColumn")')])

Worked for me on a similar issue where i had to run a query in there.

(Just in case, make sure to be on Knex latest version - as today - v1.0.7)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions