Error: cannot cast type boolean to jsonb

[uasan]

Hello.
Version postgres 3.2.4.

await sql`SELECT ${true}::jsonb`;

Result postgresql error:

Cannot cast type boolean to jsonb

[Minigugus]

Maybe an issue with parameters types inference 🤔 On the meantime, maybe you can use sql.json instead?

await sql`SELECT ${sql.json(true)}::jsonb`;

[uasan]

Maybe an issue with parameters types inference

Yes, this is an erroneous type inference, for the boolean type of JS, the serelizer to the postgres boolean type is always used, but this is not correct for JSON

On the meantime, maybe you can use sql.json instead?

Unfortunately, it is not possible to use the json method in my cases, the sql query is dynamically built and at the time of construction, I do not know the type of the parameter

[Minigugus]

I do not know the type of the parameter

Ok but your cast to ::jsonb will make postgres call JSON.stringify anyway, so where is the problem? sql.json just returns an object that tells postgres which oid to use in the query, so if you always cast to JSONB it should work I guess

[uasan]

your cast to ::jsonb will make postgres call JSON.stringify anyway, so where is the problem?

The problem is that the postgres.js does not call JSON.stringify, it passes boolean values true as 't', that's why I created this issue )

[porsager]

@uasan oh shoot, that's actually not the only issue. Same goes for the other inferred types (Date, Buffer, Bigint) :-( I should have caught this for the v3 update and completely removed inferType (

postgres/src/types.js

Line 213 in 218a7d4

export const inferType = function inferType(x) {

) to let PostgreSQL decide :-/ Changing this would require a major bump, but my initial idea right now is that it would be the best direction.

[uasan]

You, like many good developers, simply do not have enough tests for many cases, you need to think about how the community can help you write tests

[porsager]

It was really an assumption on my part wrt. handling of values after supporting the PostgreSQL feedback, but I guess you can say I might have caught it if I had had a test case for these json situations. Hindsight is 20/20 ;)

Currently a simple test case I have now is enabling this behaviour which prevents json from working out of the box:

sql`select ${ true } as x` // [{ x: true }]

So we can't really have both, but I think it would have been more correct to support the json case, and accept that input without explicit types just becomes strings? Eg this would have been better behavior:

sql`select ${ true } as x` // [{ x: 'true' }] notice `true` is a string
sql`select ${ true }::boolean as x` // [{ x: true }]
sql`select ${ true }::jsonb as x` // [{ x: true }]

But.. still initial thinking - might feel differently after sleeping on it :P

[uasan]

select ${ true } as x

I think the best behavior in cases of unknown data type from postgres describe - direct mapping of JS types to Postgres types, i.e.

JS boolean -> Postgres boolean
JS number -> Postgres numeric
JS bigint -> Postgres numeric

[AkeleyUA]

@porsager Hi. Should we expect a fix soon? If yes, how long?

P.S. I don't want to raise, but this is a pretty critical issue for us. Wish you happy coding.

[porsager]

@AkeleyUA the issue is that we can't really change it without potentially breaking someone who relies on the implicit inference. It would require a new major, and I haven't thought through if that's the way to go yet. Could you maybe describe your specific issue / show an example query, and we can see if there's a way to work around it?

[uasan]

show an example query, and we can see if there's a way to work around it?

Dynamic query builder that knows the column name and values, but does not know the type of the column

sql`UPDATE table SET "${sql(name)}" = ${value}`

potentially breaking someone who relies on the implicit inference

I'm pretty sure no one is relying on true boolean when json is expected, I'm pretty sure this bug is not reproducible now because everyone is using the old sql.json() helper interface, I don't think fixing this bug will break backwards compatibility.

[porsager]

I think It's useful when calling functions that don't do the same implicit type casts as regular queries. I'm pretty sure someones setup will break if changing it, and the fact that it's an explicit functionality tested for, it would definitely require a major bump (but I don't mind if that's necessary).

So if we make that change, it would be removing all implicit type inference from js types supplied in the Parse message and rely completely on the PostgreSQL ParameterDescription. The actual code fix could just be changing the inferType function to x => x instanceof Parameter ? x.type : 0.

I'll try to see if I can find any good reasons not to do it 😉

[uasan]

Inference data type for Postgres from the data type of JS, has sense only if the ParameterDescription returned an unknown type, if I'm wrong, give an example that proves you are right, I don't argue, I'm even curious)

[porsager]

Yeah I'm very curious too ☺️

I think it's not possible because the types specified from our end are supplied in the Parse message which comes before the ParameterDescription message. So there is no way for us to tell Postgres "ok you don't know the type so we would actually like to use x".