Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 133229
Status: open
Priority: 0/
Queue: DBD-Pg
People
Owner: greg [...] turnstep.com
Requestors: v13mx2 [...] fyvzl.net
Cc:
AdminCc:
Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

History Show all quoted text
  Tue Aug 25 20:36:14 2020 v13mx2 [...] fyvzl.net - Ticket created
Subject: Please accept "" for boolean false value
Date: Wed, 26 Aug 2020 01:18:51 +0100
To: bug-dbd-pg [...] rt.cpan.org
From: Ian Jackson <v13mx2 [...] fyvzl.net>
Perl's canonical representation for booleans is 1 for true and '' for false. This is returned by all of Perl's boolean operators such as ==, eq, &&, ||, and, or, !, etc. But attempting to pass such a value to DBD::Pg for use in a boolean column results in this error: DBD::Pg::st execute failed: ERROR: invalid input syntax for type boolean: "" This is quite unfortunate. AFAICT from the docs, DBD::Pg already accepts many different values for false, and it seems that accepting '' too would be straightforward. There doesn't seem to be a downside. In the meantime, the workaround is to pass $somebool + 0 rather than $somebool + 1 in boolean columns. -- Ian Jackson <ijackson@chiark.greenend.org.uk> These opinions are my own. Pronouns: they/he. If I emailed you from @fyvzl.net or @evade.org.uk, that is a private address which bypasses my fierce spamfilter.
  Tue Sep 01 16:18:25 2020 greg [...] turnstep.com - Status changed from 'new' to 'open'
  Tue Sep 01 16:18:25 2020 greg [...] turnstep.com - Taken
  Wed Sep 02 12:56:45 2020 greg [...] turnstep.com - Correspondence added
We do map an empty string to FALSE, but DBD::Pg needs to know that the column is boolean. The best way to do that is to use bind_param: $sth=$dbh->prepare('INSERT INTO mytable(id,val,mybool) VALUES (?,?,?)'); $sth->bind_param(3,0,SQL_BOOLEAN); This tells DBD::Pg to bind the value "0" to the third placeholder, using a boolean type. Once that is done, you can call execute as normal and it will transform your value to a true boolean (in this case it sends the string 'FALSE' over the wire: $sth->execute(123,'some text', ''); I just added some tests in t/12placeholders.t that further demonstrate the difference. (search for 'Inserting into a boolean column with an empty string') For the record here, the mapping is done in quote.c and our current list of what is "false" is: /* Things that are false: f, F, 0, false, FALSE, 0, zero-length string */