RapidMiner

Writing a Postgresql boolean column

SOLVED
Regular Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
RMStaff
Solution
Accepted by topic author rachel_lomasky
‎07-14-2017 10:08 AM

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
2 REPLIES
Moderator

Re: Writing a Postgresql boolean column

Highlighted
RMStaff
Solution
Accepted by topic author rachel_lomasky
‎07-14-2017 10:08 AM

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