Search code examples
postgresqlcastingbooleanvarchar

how to cast varchar to bool postgresql

I need to read value from one table as varchar and write it in another one as boolean. So that any label (text) other than "False" is treated as "True". But I also want the converting (cast) work - ::bool. I have example:

case when COALESCE(NULLIF('#(OG)', '')::bool, false) = FALSE then FALSE else TRUE end

But it gives mistake, because in my table I have not perfect data such as: f, false, n, no, off, 0 and t, true, y, yes, on, 1, but also simple text - 123.

invalid input syntax for type boolean: "123"

I work with EXEL file and all I do I do in file.manifest:

<?xml version="1.0" encoding="utf-8"?>
<Manifest>
<File startRow="2" table="td_actualpayments">
    <add isSql="false" key="OG" cell="G" header="Contractor OG" />
    <add isSql="true" key="ContractorIsGroupSociety" dataType="subquery" query="(case when COALESCE(NULLIF('#(OG)', '')::bool, false) = FALSE then FALSE else TRUE end)" />
  </File>
<PostUpdate>
Solution

  • You could use a regular expression, like

    NOT COALESCE(textcol, '') ~* E'^((f(a(l(se?)?)?)?)|(off?)|(no?)|0?)$'

    That should return FALSE for NULL, the empty string and every valid string representation of FALSE, and it should return TRUE for all other strings.