Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: column is of type boolean but expression is of type character varying

  1. #1

    Default column is of type boolean but expression is of type character varying

    Hi,

    i want to update table in database. Column of this table is boolean type. I used add constant step with name=person_active, type=boolean, value=1 or value=TRUE. When i run my transformation following error shows up:

    column "person_active" is of type boolean but expression is of type character varying

    Can you help me pls?

    Thx

    Paul

  2. #2

    Default

    Hi Paul,

    Could you give some more information : type of database, kettle version, the exact error message.
    And maybe post the transformation.

    Bart

  3. #3

    Default

    Quote Originally Posted by Bart.Farasyn View Post
    Hi Paul,

    Could you give some more information : type of database, kettle version, the exact error message.
    And maybe post the transformation.

    Bart
    Database is PostgreSQL 8.2, kettle version is 3.0.1 and error message is:

    Code:
    Error in step, asking everyone to stop because of:
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error inserting row
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR: column "person_active" is of type boolean but expression is of type character varying
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1333)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1220)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.update.Update.lookupValues(Update.java:158)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.update.Update.processRow(Update.java:246)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.update.Update.run(Update.java:468)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Caused by: org.postgresql.util.PSQLException: ERROR: column "person_active" is of type boolean but expression is of type character varying
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1283)
    2008/07/14 10:47:03 - Update.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     ... 4 more

  4. #4

    Default problem solved

    i used older drivers of postgresql in kettle. i dont understand why is not working driver in new kettle.

  5. #5
    Join Date
    Aug 2007
    Posts
    25

    Default boolean

    Hello,

    i am not realy shure, but i think i had the same problem little time ago.

    i mentioned that something ;-) (may be kettle) change the 'ture' in boolean to a 'Y' (a character ?!?).

    i choosed the type string, value 'true'. And then the data set was updated. (May be than an internal conversion from sting to boolean, when inserting/ updating the row).

    try this, may be it works for you too.

    regards
    sebastian

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The discussion has come up many, many, many times on this forum, perhaps you can search for it.

    The gist of it is that boolean is a non-standard SQL keyword (a.k.a. BIT, etc).
    Normally from what the docs say, PG accepts 'Y'/'N', 'true'/'false', 1/0 etc as values for a boolean, but their JDBC driver chokes on it.

    I think you can simply convert to integer to get things inserted though.

  7. #7

    Thumbs up

    Matt,

    thank you for the answer on this thread. It helped a lot, even if it's an old answer...

    Thanks
    Mark
    Regards
    Mark

  8. #8
    Join Date
    Aug 2008
    Posts
    3

    Default

    Sorry, but i don't understand what MattCasters wanted to say by:
    "you can simply convert to integer"

    How can i convert it to a integer before the insertion?
    Should i change my database schema?

    thanks in advance

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Use a "Select Values" step to change the data type from "Boolean" to "Integer". (the Metadata tab)

  10. #10

    Default

    I found this thread from Google, so I thought I would share a quick update that I found helpful.

    The latest versions of Pentaho Data Integration now have a checkbox option in the database connection settings that says "Supports Boolean data type". Simply checking this box and rerunning the transformation fixes the issue for me.

    Hope this helps some future Googler :-)

  11. #11
    Join Date
    Aug 2012
    Posts
    2

    Default

    thanks topherfangio, select values construct didn't work for me but your solution worked pretty well. I am using PG and it supports that.

  12. #12
    Join Date
    Apr 2012
    Posts
    2

    Default

    Quote Originally Posted by topherfangio View Post
    I found this thread from Google, so I thought I would share a quick update that I found helpful.

    The latest versions of Pentaho Data Integration now have a checkbox option in the database connection settings that says "Supports Boolean data type". Simply checking this box and rerunning the transformation fixes the issue for me.

    Hope this helps some future Googler :-)
    Works great, thanks!

    Not sure what Pentaho is doing wrong, as copy paste the statement (using 'Y') from the error into SQL Explorer and it runs fine!

  13. #13
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The thread was 4 years old. The risk of hijacking those is that you get bad advise. :-)

  14. #14
    Join Date
    Aug 2008
    Posts
    3

    Default

    Quote Originally Posted by MattCasters View Post
    The thread was 4 years old. The risk of hijacking those is that you get bad advise. :-)
    I agree, but this thread is a counter example. "Supports Boolean data type" really seems to fix the problem.
    I wonder if there is any downside of having this option checked... If not, why isn't it checked by default?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.