how to cast varchar to bool postgresql

Question

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>

please post your table structure and not some abstract query example, but the one that gives you the error. also, elaborate what "want the cast work ::bool" means — Vao Tsun
– Vao Tsun, Commented Sep 19, 2017 at 7:34

Laurenz Albe · Accepted Answer · 2017-09-19 08:42:35Z

1

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.

answered Sep 19, 2017 at 8:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to cast varchar to bool postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related