RapidMiner

Contributor II rachel_lomasky
Contributor II

Writing a Postgresql boolean column

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

2 REPLIES
RM Certified Expert
RM Certified Expert

Re: Writing a Postgresql boolean column

Highlighted
Community Manager Community Manager
Community Manager
Solution

Re: Writing a Postgresql boolean column

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

--
Balázs Bárány
Data Scientist, Vienna
https://datascientist.at
Polls
How can RapidMiner increase participation in our new competitions?
Twitter Feed