View Revisions: Issue #7094

Back to Issue
Summary 0007094: default.values.to.translate not being applied for bit and Boolean types on Postgres
Revision 2025-10-15 17:04 by mdrouard
Steps To Reproduce 1. Setup MSSQL and Postgres DB instances
2. Add the following table to MSSQL DB

CREATE TABLE [dbo].[test_bit2](
    id [int] NOT NULL,
    bite [bit] NOT NULL DEFAULT 'False',
 CONSTRAINT [PK_test_bit2] PRIMARY KEY CLUSTERED (id )
);
INSERT INTO test_bit2 (id, bite) VALUES(1, 'true');
INSERT INTO test_bit2 (id) VALUES(2);

3. Setup replication from MSSQL -> Postgres with table "test_bit2"
4. Set dataloader.create.table.without.defaults.on.error=false (This error will still occur without setting this to false, but the table will fallback and create without defaults)
5. Send an initial load from MSSQL to Postgres with "Create tables or alter them to match source"
6. Observe the following in the log:
WARN [Endpoint_2] [JdbcSqlTemplate] [endpoint_2-dataloader-20] ERROR: invalid input syntax for type boolean: "'False'"
  Position: 90. Failed to execute: CREATE TABLE "public"."test_bit2"(
    "id" INTEGER NOT NULL,
    "bite" BOOLEAN DEFAULT '''False''' NOT NULL,
    PRIMARY KEY ("id")
)
Revision 2025-10-15 17:22 by mdrouard
Steps To Reproduce 1. Setup MSSQL and Postgres DB instances
2. Add the following table to MSSQL DB

CREATE TABLE [dbo].[test_bit2](
    id [int] NOT NULL,
    bite [bit] NOT NULL DEFAULT 'False',
 CONSTRAINT [PK_test_bit2] PRIMARY KEY CLUSTERED (id )
);
INSERT INTO test_bit2 (id, bite) VALUES(1, 'true');
INSERT INTO test_bit2 (id) VALUES(2);

3. Setup replication from MSSQL -> Postgres with table "test_bit2"
4. Set dataloader.create.table.without.defaults.on.error=false (This error will still occur without setting this to false, but the table will fallback and create without defaults)
4.1. Set default.values.to.translate="'false'=0","'true'=1","'False'=0","'True'=1"
5. Send an initial load from MSSQL to Postgres with "Create tables or alter them to match source"
6. Observe the following in the log:
WARN [Endpoint_2] [JdbcSqlTemplate] [endpoint_2-dataloader-20] ERROR: invalid input syntax for type boolean: "'False'"
  Position: 90. Failed to execute: CREATE TABLE "public"."test_bit2"(
    "id" INTEGER NOT NULL,
    "bite" BOOLEAN DEFAULT '''False''' NOT NULL,
    PRIMARY KEY ("id")
)