Writing a Postgresql boolean column

rachel_lomaskyrachel_lomasky Member Posts: 52 Guru
edited December 2018 in Help

Hi,

 

I am using the Write Database operator to write a Postgres database:

<?xml version="1.0" encoding="UTF-8"?><process version="7.4.000">
<operator activated="true" class="jdbc_connectors:write_database" compatibility="7.4.000" expanded="true" height="68" name="write to my table" width="90" x="1452" y="238">
<parameter key="define_connection" value="url"/>
<parameter key="connection" value="AWS-local"/>
<parameter key="database_system" value="PostgreSQL"/>
<parameter key="database_url" value="jdbc:postgresql://%{server}:5432/wevo_staging"/>
<parameter key="username" value="%{db_user}"/>
<parameter key="password" value="TKvfmaicwj2LFPeZysdUxw=="/>
<parameter key="use_default_schema" value="false"/>
<parameter key="schema_name" value="myschema"/>
<parameter key="table_name" value="mytable"/>
<parameter key="overwrite_mode" value="append"/>
<parameter key="set_default_varchar_length" value="false"/>
<parameter key="default_varchar_length" value="128"/>
<parameter key="add_generated_primary_keys" value="false"/>
<parameter key="db_key_attribute_name" value="generated_primary_key"/>
<parameter key="batch_size" value="100"/>
<description align="center" color="transparent" colored="false" width="126">Insert into mytable</description>
</operator>
</process>

The table I am inserting into has a boolean column:

create table myschema.mytable
(
id bigserial not null
constraint mytable_pk
unique,
col1 integer,
col2 integer,
report boolean
)
;

I have prepared my column to be binomial:

Screen Shot 2017-07-11 at 2.04.02 PM.png

When I try to do the insert, I get an error:

Jul 11, 2017 1:54:55 PM SEVERE: Process failed: Database error occurred: Batch entry 0 INSERT INTO "myschema"."mytable" ("col1", "col2", "report") VALUES (2.0, 1.0, 'false') was aborted.  Call getNextException to see the cause.

I have also tried to insert it using the strings {"true", "false"}.

 

When I try the insert without the boolean column, all is well. 

 

Is there a different data type I should be using?

 

Thanks,

Rachel

Tagged:

Best Answer

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    Solution Accepted

    Hi,

    PostgreSQL is a bit strict by default when getting strings in prepared statements, which is what RapidMiner uses.

    This can be changed in the database properties under Advanced:

    Set the "stringtype" parameter to "unspecified" instead of varchar. In that case, the JDBC driver will accept the quoted values ('true', 'false') and convert them to the boolean values.

    There's no downside to this setting as far as I know. It might do wrong type conversions in very special cases but I don't think you can even trigger that with RapidMiner.

     

    Regards,

    Balázs

Answers

Sign In or Register to comment.