I am trying to migrate data from an on prem SQL Server DB to Postgres Aurora using AWS DMS. The data migrates just fine, but for tables that have a boolean column in the primary key, during validation it fails with the following error:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
Upon checking the logs on the Postgres side, I find this:
ERROR: invalid input syntax for type boolean: "" at character <number>
STATEMENT: SELECT cast ("pcode" as varchar(6)) , "other_columns" , "boolean_column" FROM "db"."table" WHERE ((("boolean_column" = ' ' AND "pcode" > 'L7L3V9') AND ("boolean_column" = ' ' AND "pcode" <= 'L8L4E8'))) ORDER BY "boolean_column" ASC , "pcode" ASC
During validation, it's fetching the records from the postgres in batches, and for each batch it uses the wrong value for the "bolean_column" (comparing to '' - blank string). I am not sure why it's doing this, or how to influence this behaviour so that the validations will be performed successfully.
The boolean column is a:
- bit field on the SQL Server side
- on postgres side, have tried converting to (both with same validation error as above):
- numeric with precision 1
- boolean
OFICIAL DE AWSActualizada hace 2 años
Could you pls. confirm if both column ( i am assuming table composite primary key not unique key with two column in them) not NOT NULL. You said primary key just wanted to be double sure. If one of the column is null able, validation would not be possible, it is not supported. MSSQL - BIT to Boolean seems right conversion.