sql.array error on empty array

[joshxyzhimself]

I got a "projectIds" field if integer[] with the following query:

await sql`
      UPDATE
        users
      SET
        "projectIds" = ${sql.array(projectIds)}
      WHERE
        "id" = ${userId}
      ;
`

It works when it's not empty, but when empty I'm getting the following error:

column "projectIds" is of type integer[] but expression is of type text[]

Any way to fix this? Thank you.

[joshxyzhimself]

ok digging deeper ive found out that

console.log(sql.array([]));
// { type: 25, array: true, value: [] }

console.log(sql.array([1, 2, 3]));
// { type: 1700, array: true, value: [ 1, 2, 3 ] }

console.log(sql.array(['a', 'b', 'c']));
// { type: 25, array: true, value: [ 'a', 'b', 'c' ] }

So temporary workaround is:

const projectIds = [];
projectIds.type = 1700;

await sql`
      UPDATE
        users
      SET
        "projectIds" = ${sql.array(projectIds)}
      WHERE
        "id" = ${userId}
      ;
`

Using the type property check at

postgres/lib/types.js

Line 105 in 758b5ee

return (x && x.type) || (x instanceof Date

referenced at

postgres/lib/index.js

Lines 220 to 226 in 758b5ee

	function array(value) {
	return {
	type: inferType(value) || 25,
	array: true,
	value
	}
	}

Which is quite gruesome since I gotta remember the type number.

Is it possible to have an optional second parameter of string type, accepting number, string, date, or buffer to override the default string array type in case it gets an empty array? Thank you.

Kinda like

${sql.array([], 'string')}

[porsager]

Would you mind trying master? npm install porsager/postgres, as i think this has been fixed, and i just need to get a new version pushed 😊

[joshxyzhimself]

Hi @porsager,

It broke some unrelated parts unfortunately. I had this query before, it used to return plain integers before, but now I'm getting BigInt which is quite unexpected.

  const [transactionsPerPhase] = await sql`
    SELECT
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 0
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase0",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 1
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase1",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 2
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase2",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 3
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase3",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 4
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase4",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 5
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase5",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 6
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase6",
      (SELECT
        COUNT(transactions)
        FROM transactions
        WHERE "phaseId" = 7
        AND "projectId" IN (${session.user.projectIds})
      ) AS "inPhase7";
  `;
  return Object.values(transactionsPerPhase);

  transactionsPerPhase: [
    1n, 0n, 0n, 0n,
    0n, 0n, 0n, 0n
  ]

[joshxyzhimself]

Related

/* eslint-disable no-console */

const sql = require('../db');

(async () => {
  const projectIds = [];
  await sql`
    UPDATE
      users
    SET
      "projectIds" = ${sql.array(projectIds)}
    WHERE
      "id" = ${5}
    ;
  `;
})().then(console.log).catch(console.error);

Error: column "projectIds" is of type integer[] but expression is of type text[]
    at Object.generic (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\types.js:212:5)
    at Object.ErrorResponse (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\backend.js:128:33)
    at TLSSocket.data (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\connection.js:207:25)
    at TLSSocket.emit (events.js:321:20)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at TLSSocket.Readable.push (_stream_readable.js:214:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:186:23)
    at cachedError (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\index.js:189:25)
    at query (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\index.js:164:56)
    at postgres (C:\cygwin64\home\1234\transac\node_modules\postgres\lib\index.js:65:12)
    at C:\cygwin64\home\1234\transac\server\queries\insertDefaults.js:7:12 {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You will need to rewrite or cast the expression.',
  position: '49',
  file: 'parse_target.c',
  line: '583',
  routine: 'transformAssignedExpr'
}

[porsager]

Sorry, that's right. It's probably going to return string and not BigInt later on once I've made some more adjustments, and yes, this will be a 2.0 release with breaking changes, where this one is the biggest one.

[porsager]

I'll look into why the type doesn't fallback to letting Postgres decide for arrays.