This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new fede7e3fe5 postgresql bulkloader boolean support fix, fixes 5912
(#6994)
fede7e3fe5 is described below
commit fede7e3fe53f5fbefea0188d49755a44fbc76a53
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Mon Apr 13 22:08:12 2026 +0200
postgresql bulkloader boolean support fix, fixes 5912 (#6994)
---
.mvn/jvm.config | 4 +-
.../pipeline/transforms/postgresbulkloader.adoc | 2 +-
.../0039-postgresql-bulkloader-boolean.hpl | 133 +++++++++++++++++++++
.../datasets/golden-table-compare-general.csv | 4 +-
.../main-0039-postgresql-bulkloader-boolean.hwf | 133 +++++++++++++++++++++
.../script_postgresql_bulkloader_boolean.sql | 24 ++++
.../transforms/pgbulkloader/PGBulkLoader.java | 24 +++-
.../transforms/pgbulkloader/PGBulkLoaderTest.java | 17 +++
8 files changed, 332 insertions(+), 9 deletions(-)
diff --git a/.mvn/jvm.config b/.mvn/jvm.config
index 19d754323c..f061e563ec 100644
--- a/.mvn/jvm.config
+++ b/.mvn/jvm.config
@@ -16,4 +16,6 @@
--add-opens java.base/sun.security.action=ALL-UNNAMED
--add-opens java.base/sun.util.calendar=ALL-UNNAMED
--add-opens java.security.jgss/sun.security.krb5=ALL-UNNAMED
---add-exports java.base/sun.nio.ch=ALL-UNNAMED
\ No newline at end of file
+--add-exports java.base/sun.nio.ch=ALL-UNNAMED
+-Dorg.slf4j.simpleLogger.log.org.jacoco.maven.AgentMojo=warn
+-Dorg.slf4j.simpleLogger.log.org.jacoco.maven.AgentITMojo=warn
\ No newline at end of file
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/postgresbulkloader.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/postgresbulkloader.adoc
index 31b6175732..d6eb17e449 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/postgresbulkloader.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/postgresbulkloader.adoc
@@ -27,7 +27,7 @@ under the License.
The PostgreSQL Bulk Loader transform streams data from Hop to Postgresql using
https://www.postgresql.org/docs/current/sql-copy.html["COPY DATA FROM STDIN"^]
into the database.
-TIP: replace boolean fields in your pipeline stream by string fields with "Y"
or "N" values to avoid errors.
+TIP: boolean stream fields are serialized as `t` or `f`, which PostgreSQL
`COPY` accepts for boolean columns.
|
== Supported Engines
diff --git a/integration-tests/database/0039-postgresql-bulkloader-boolean.hpl
b/integration-tests/database/0039-postgresql-bulkloader-boolean.hpl
new file mode 100644
index 0000000000..fb84d5b79d
--- /dev/null
+++ b/integration-tests/database/0039-postgresql-bulkloader-boolean.hpl
@@ -0,0 +1,133 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0039-postgresql-bulkloader-boolean</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/04/13 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/04/13 12:00:00.000</modified_date>
+ <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
+ <is_key_private>N</is_key_private>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>test data</from>
+ <to>PostgreSQL Bulk Loader</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>PostgreSQL Bulk Loader</name>
+ <type>PGBulkLoader</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>unit-test-db</connection>
+ <delimiter>;</delimiter>
+ <enclosure>"</enclosure>
+ <load_action>TRUNCATE</load_action>
+ <mapping>
+ <date_mask/>
+ <field_name>id</field_name>
+ <stream_name>id</stream_name>
+ </mapping>
+ <mapping>
+ <date_mask/>
+ <field_name>active</field_name>
+ <stream_name>is_active</stream_name>
+ </mapping>
+ <schema>public</schema>
+ <stop_on_error>N</stop_on_error>
+ <table>pg_bulk_bool_test</table>
+ <attributes/>
+ <GUI>
+ <xloc>464</xloc>
+ <yloc>128</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>test data</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <set_empty_string>N</set_empty_string>
+ <length>-1</length>
+ <name>id</name>
+ <precision>-1</precision>
+ <type>String</type>
+ </field>
+ <field>
+ <set_empty_string>N</set_empty_string>
+ <length>-1</length>
+ <name>active</name>
+ <precision>-1</precision>
+ <type>Boolean</type>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>row1</item>
+ <item>true</item>
+ </line>
+ <line>
+ <item>row2</item>
+ <item>false</item>
+ </line>
+ <line>
+ <item>row3</item>
+ <item>true</item>
+ </line>
+ </data>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>128</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/database/datasets/golden-table-compare-general.csv
b/integration-tests/database/datasets/golden-table-compare-general.csv
index eb4a7b3789..366dee3776 100644
--- a/integration-tests/database/datasets/golden-table-compare-general.csv
+++ b/integration-tests/database/datasets/golden-table-compare-general.csv
@@ -1,2 +1,2 @@
-ref_schema,ref_table,cmp_schema,cmp_table,id_fields,excl_fields,key_description,ref_value,compare_value,nrErrors,nrRecordsReferenceTable,nrRecordsCompareTable,nrErrorsLeftJoin,nrErrorsInnerJoin,nrErrorsRightJoin
-public,reference_table,,compare_table,id,,,,,3,3,3,0,1,2
+ref_schema,ref_table,cmp_schema,cmp_table,id_fields,excl_fields,key_description,ref_value,compare_value,nrErrors,nrRecordsReferenceTable,nrRecordsCompareTable,nrErrorsLeftJoin,nrErrorsInnerJoin,nrErrorsRightJoin
+public,reference_table,,compare_table,id,,,,,3,3,3,1,1,1
diff --git
a/integration-tests/database/main-0039-postgresql-bulkloader-boolean.hwf
b/integration-tests/database/main-0039-postgresql-bulkloader-boolean.hwf
new file mode 100644
index 0000000000..aa1f54e7c8
--- /dev/null
+++ b/integration-tests/database/main-0039-postgresql-bulkloader-boolean.hwf
@@ -0,0 +1,133 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0039-postgresql-bulkloader-boolean</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/04/13 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/04/13 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>80</xloc>
+ <yloc>80</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>SQL</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql/>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>T</sqlfromfile>
+
<sqlfilename>${PROJECT_HOME}/scripts/script_postgresql_bulkloader_boolean.sql</sqlfilename>
+ <sendOneStatement>F</sendOneStatement>
+ <connection>unit-test-db</connection>
+ <parallel>N</parallel>
+ <xloc>288</xloc>
+ <yloc>80</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0039-postgresql-bulkloader-boolean.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+
<filename>${PROJECT_HOME}/0039-postgresql-bulkloader-boolean.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>496</xloc>
+ <yloc>80</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Abort workflow</name>
+ <description/>
+ <type>ABORT</type>
+ <attributes/>
+ <always_log_rows>N</always_log_rows>
+ <parallel>N</parallel>
+ <xloc>752</xloc>
+ <yloc>80</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>SQL</from>
+ <to>0039-postgresql-bulkloader-boolean.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>Start</from>
+ <to>SQL</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>0039-postgresql-bulkloader-boolean.hpl</from>
+ <to>Abort workflow</to>
+ <enabled>Y</enabled>
+ <evaluation>N</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git
a/integration-tests/database/scripts/script_postgresql_bulkloader_boolean.sql
b/integration-tests/database/scripts/script_postgresql_bulkloader_boolean.sql
new file mode 100644
index 0000000000..b3e132f3aa
--- /dev/null
+++
b/integration-tests/database/scripts/script_postgresql_bulkloader_boolean.sql
@@ -0,0 +1,24 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file to
+you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+ http://www.apache.org/licenses/LICENSE-2.0
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+*/
+
+DROP TABLE IF EXISTS public.pg_bulk_bool_test;
+
+CREATE TABLE public.pg_bulk_bool_test
+(
+ id varchar NULL,
+ is_active boolean NULL
+);
diff --git
a/plugins/transforms/pgbulkloader/src/main/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoader.java
b/plugins/transforms/pgbulkloader/src/main/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoader.java
index 8aaa640504..cc8ec34f08 100644
---
a/plugins/transforms/pgbulkloader/src/main/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoader.java
+++
b/plugins/transforms/pgbulkloader/src/main/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoader.java
@@ -37,6 +37,7 @@ import org.apache.hop.core.Const;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopValueException;
import org.apache.hop.core.logging.ILoggingObject;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
@@ -267,6 +268,20 @@ public class PGBulkLoader extends
BaseTransform<PGBulkLoaderMeta, PGBulkLoaderDa
}
}
+ /**
+ * Encodes a boolean for PostgreSQL COPY text format. {@code t}/{@code f}
are accepted; literals
+ * like {@code 1.0} from a numeric conversion are not.
+ */
+ @VisibleForTesting
+ static byte[] booleanFieldBytesForPgCopyText(
+ IValueMeta valueMeta, Object valueData, Charset charset) throws
HopValueException {
+ Boolean bool = valueMeta.getBoolean(valueData);
+ if (bool == null) {
+ return null;
+ }
+ return (bool ? "t" : "f").getBytes(charset);
+ }
+
private void writeRowToPostgres(IRowMeta rowMeta, Object[] r) throws
HopException {
try {
@@ -392,11 +407,10 @@ public class PGBulkLoader extends
BaseTransform<PGBulkLoaderMeta, PGBulkLoaderDa
}
break;
case IValueMeta.TYPE_BOOLEAN:
- if (valueMeta.isStorageBinaryString()) {
- pgCopyOut.write((byte[]) valueData);
- } else {
- pgCopyOut.write(
-
Double.toString(valueMeta.getNumber(valueData)).getBytes(clientEncoding));
+ byte[] boolBytes =
+ booleanFieldBytesForPgCopyText(valueMeta, valueData,
clientEncoding);
+ if (boolBytes != null) {
+ pgCopyOut.write(boolBytes);
}
break;
case IValueMeta.TYPE_NUMBER:
diff --git
a/plugins/transforms/pgbulkloader/src/test/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoaderTest.java
b/plugins/transforms/pgbulkloader/src/test/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoaderTest.java
index b3bd38e4be..7e386f292a 100644
---
a/plugins/transforms/pgbulkloader/src/test/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoaderTest.java
+++
b/plugins/transforms/pgbulkloader/src/test/java/org/apache/hop/pipeline/transforms/pgbulkloader/PGBulkLoaderTest.java
@@ -17,9 +17,11 @@
package org.apache.hop.pipeline.transforms.pgbulkloader;
+import static org.junit.jupiter.api.Assertions.assertArrayEquals;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;
import static org.mockito.ArgumentMatchers.any;
@@ -30,6 +32,7 @@ import static org.mockito.Mockito.spy;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
+import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import org.apache.hop.core.HopClientEnvironment;
import org.apache.hop.core.database.Database;
@@ -37,9 +40,11 @@ import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.database.DatabaseMetaPlugin;
import org.apache.hop.core.database.DatabasePluginType;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopValueException;
import org.apache.hop.core.exception.HopXmlException;
import org.apache.hop.core.logging.ILoggingObject;
import org.apache.hop.core.plugins.PluginRegistry;
+import org.apache.hop.core.row.value.ValueMetaBoolean;
import org.apache.hop.databases.postgresql.PostgreSqlDatabaseMeta;
import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
@@ -101,6 +106,18 @@ class PGBulkLoaderTest {
transformMockHelper.cleanUp();
}
+ @Test
+ void booleanFieldBytesForPgCopyText_usesPostgresAcceptableLiterals() throws
HopValueException {
+ ValueMetaBoolean meta = new ValueMetaBoolean("active");
+ assertArrayEquals(
+ "t".getBytes(StandardCharsets.UTF_8),
+ PGBulkLoader.booleanFieldBytesForPgCopyText(meta, Boolean.TRUE,
StandardCharsets.UTF_8));
+ assertArrayEquals(
+ "f".getBytes(StandardCharsets.UTF_8),
+ PGBulkLoader.booleanFieldBytesForPgCopyText(meta, Boolean.FALSE,
StandardCharsets.UTF_8));
+ assertNull(PGBulkLoader.booleanFieldBytesForPgCopyText(meta, null,
StandardCharsets.UTF_8));
+ }
+
@Test
void testCreateCommandLine() throws Exception {
PGBulkLoaderMeta meta = mock(PGBulkLoaderMeta.class);
