Writing a Postgresql boolean column

rachel_lomasky

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:

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

BalazsBarany

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

Thomas_Ott

@BalazsBarany any idea?

BalazsBarany

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