boolean type acting odd with unnest

[alexhwoods]

Hey guys! Thank you for this awesome library; we love using it.

I think I've found an issue with unnest, although it could definitely be in my usage.

Expected Behavior

A query like the following should work fine:

await pool.any(
  sql`
    select bar from ${sql.unnest([[true]], ['boolean'])} as foo(bar)
  `,
)

Current Behavior

It gives the error:

{
      "type": "DatabaseError",
      "message": "type \"boolean[]\" does not exist",
      "stack":
          error: type "boolean[]" does not exist
              at Parser.parseErrorMessage (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:357:11)
              at Parser.handlePacket (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:186:21)
              at Parser.parse (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:101:30)
              at Socket.<anonymous> (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/index.ts:7:48)
              at Socket.emit (node:events:394:28)
              at Socket.emit (node:domain:475:12)
              at addChunk (node:internal/streams/readable:315:12)
              at readableAddChunk (node:internal/streams/readable:289:9)
              at Socket.Readable.push (node:internal/streams/readable:228:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:199:23)
      "length": 96,
      "name": "error",
      "severity": "ERROR",
      "code": "42704",
      "position": "35",
      "file": "parse_type.c",
      "line": "274",
      "routine": "typenameType",
      "notices": []
    }

Possible Solution

You guys would know better than I would, but I think the boolean type is being interpreted incorrectly with unnest.

here is a workaround:

await pool.any(
  sql`
    select bar::boolean from ${sql.unnest([[true]], ['text'])} as foo(bar)
  `,
)

[nponiros]

Have you tried using 'bool' instead of 'boolean'? My experience so far is that unnest doesn't always use the same type names as the ones used for table definitions. Unfortunately I still don't understand how to map a type in a table definition to a type for unnest.

[alexhwoods]

Interesting, that does work!

Yeah I would love some way to know which type to use 😅

For example this has the same problem:

await pool.any(
    sql`
      select bar from ${sql.unnest([[5]], ['integer'])} as foo(bar)
    `,
  )

{
      "type": "DatabaseError",
      "message": "type \"integer[]\" does not exist",
      "stack":
          error: type "integer[]" does not exist
              at Parser.parseErrorMessage (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:357:11)
              at Parser.handlePacket (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:186:21)
              at Parser.parse (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/parser.ts:101:30)
              at Socket.<anonymous> (/Users/alexwoods/makeswift/cosmos/node_modules/pg-protocol/src/index.ts:7:48)
              at Socket.emit (node:events:394:28)
              at Socket.emit (node:domain:475:12)
              at addChunk (node:internal/streams/readable:315:12)
              at readableAddChunk (node:internal/streams/readable:289:9)
              at Socket.Readable.push (node:internal/streams/readable:228:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:199:23)
      "length": 96,
      "name": "error",
      "severity": "ERROR",
      "code": "42704",
      "position": "35",
      "file": "parse_type.c",
      "line": "274",
      "routine": "typenameType",
      "notices": []
    }

[nponiros]

I believe I used int4 for integer

[migueloller]

Looks like the “aliases” column here is what’s being used? https://www.postgresql.org/docs/9.6/datatype.html

[gajus]

What you are looking for are called "type name identifiers". It is mentioned in documentation here:

https://github.com/gajus/slonik#sqlarray-membertype

I don't know where they are listed in documentation, but you can find all of them in the source code:

https://github.com/postgres/postgres/blob/69edf4f8802247209e77f69e089799b3d83c13a4/src/include/catalog/pg_type.dat#L74-L78

You are looking for typname.

We could theoretically add a TypeScript type ENUM for typname.

[gajus]

I am pretty sure this PR improves developer experience:

#332

[gajus]

I just realized that I misread the entire thread. The issue is that (unlike sql.array), sql.unnest did not support SQL tokens. This is now addressed in #332.

[gajus]

🎉 This issue has been resolved in version 28.1.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀