I could be mistaken, as I'm just going by what I've read, but I don't think you need Perl v5.40 for this. use v5.36; use builtin qw(true false); and v5.38; use builtin qw(true false); should also work. I'm just mentioning this in case someone didn't know. 😄

starting with 5.36, even !0 produces a native boolean value which would lead to this error when passed as a bind param.

perl -Mbuiltin=true,false,is_bool -E 'no warnings experimental::builtin; say "!!0 is ".is_bool(!!0); say "false is ".is_bool(false); say "0 is ".is_bool(0)'
!!0 is 1
false is 1
0 is

You're not stuck with the builtin bools; you can explicitly pass JSON::PP::true as the bind parameter.

you can explicitly pass JSON::PP::true as the bind parameter.

But typing JSON::PP::true (or a variable containing that value) is tedious. Would be much nicer to use the builtins, else would be easier to type int before true/false/a condition evaluating to builtin boolean.

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans, I’ll just monkey-patch Mojo::Pg in my projects to achieve the desired result. But if I’m not mistaken this would be a welcome change in the Perl community. The ability to pass the native true & false as boolean field bind arguments.

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans [...]

Maintaining DBD::Pg is nobody's full-time job. What we need is someone to submit a PR. I'm sure a PR would be most welcome by all. I say this strictly as an observer and proponent of this project who has contributed a couple PRs over the years.

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans [...]

What we need is someone to submit a PR.

I think the part that needs updating is written in C/XS or something like that. I can’t do that.

I can’t do that.

I didn't say "you." I said "someone." 😄 My point was that it's a community effort.

For now I implememted a fix on my projects, by subclassing Mojo::Pg. That's good enough for me, for now.

Maybe, to avoid breaking backwards-compatibility, there could be an Option, for converting built in booleans to Pg Booleans. And maybe another option also, for converting Pg Booleans to perl's built in booleans, when issuing a SELECT query!

I'm not actually convinced that this is a bug in DBD::Pg!

After trying to write a failing test, i discovered that when a bind param is set as SQL_BOOLEAN, core bools Just Work ™️. So i would say it's the job of whatever query engine you're using to handle passing that to bind_param

Either way, it would be good if this repo decided on a course (one way or another), so that after that we can press our query engines to adapt (if DBD::Pg doesn't).

I think it would make sense to always bind native booleans as 0 and 1. It'd be a few lines' change in dbd_bind_ph.

Note that using a "real" perl boolean false (e.g. !!0) as a postgres boolean has always failed, since it stringifies to an empty string:

$ perl -MDBI -le 'print $]; DBI->connect("dbi:Pg:")->selectrow_array("select ?::bool", undef, !!0)'
5.008009
DBD::Pg::db selectrow_array failed: ERROR:  invalid input syntax for type boolean: ""
CONTEXT:  unnamed portal parameter $1 = '' at -e line 1.

Since 5.36 we can at least detect it and bind it as 0 instead, but we can't defend against people using false from builtin::Backport on older versions of Perl.

I believe binding false as 0 would be good enough for me.

#129 implements my above suggestion, and adds tests and documentation.

@ilmari Thanks, dude

I just had a thought (and pushed a commit to do it, for discussion): should we respect pg_bool_tf and bind native booleans as t/f instead of 1/0 when it's set?

I just had a thought (and pushed a commit to do it, for discussion): should we respect pg_bool_tf and bind native booleans as t/f instead of 1/0 when it's set?

Seems reasonable to me. Thanks!