View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0005354 | SymmetricDS | Bug | public | 2022-07-11 15:18 | 2022-10-03 14:38 |
Reporter | pmarzullo | Assigned To | pmarzullo | ||
Priority | normal | ||||
Status | assigned | Resolution | open | ||
Product Version | 3.9.13 | ||||
Summary | 0005354: Sybase ASE Small Int not able to be converted to Postgres boolean without transformation or casting | ||||
Description | We have a table in a Sybase database with a column defined as smallint. It either contains 0 or 1, always. On the target side, the data type was changed to boolean. There is no transformation setup as the source/target tables are identical (except for minor data type changes). The message returned is: 2020-08-28 12:55:11,045 ERROR [SPIN_SYBASE-SPIN_SYB] [AcknowledgeService] [spin_sybase-spin_syb-push-MF_PG_CLI_Q-4] The outgoing batch MM_PG-679286 failed: ERROR: column "row_del_from_src_ind" is of type boolean but expression is of type smallint Hint: You will need to rewrite or cast the expression. Position: 242 That seems like a SDS-generated message, not from the Postgres server. Postgres is very lenient as to what value can be used to populate a boolean column. It allows, 0/1, Y/N, yes/no, … The complete list is in this page: https://www.postgresql.org/docs/11/datatype-boolean.html Would it be possible for SDS to pass all values from the source as is and let the Postgres server generate the error if it is not valid? This error is new in 3.12.2. Under 3.9.13, 0/1 was correctly handled (Y/N was not). So it was handled correctly for some values in 3.9.13. | ||||
Additional Information | This is a database server error, because Postgres implements a strict datatype adherence. I can think of two ways to deal with this. 1. Use Symmetric transforms. 2. Create a Postgres database server CREATE CAST object, that casts a smallint to a boolean. The type of CAST needs to be a ASSIGNMENT type, so that no CAST() call needs to be added to the SQL statement. This is also known as an assignment cast. Here is a link that describes how to create one: https://www.postgresql.org/docs/11/sql-createcast.html | ||||
Tags | No tags attached. | ||||
Date Modified | Username | Field | Change |
---|---|---|---|
2022-07-11 15:18 | pmarzullo | New Issue | |
2022-07-11 15:18 | pmarzullo | Status | new => assigned |
2022-07-11 15:18 | pmarzullo | Assigned To | => pmarzullo |
2022-07-11 15:18 | pmarzullo | File Added: MM_POSTGRES-MM_PG-20200828162046.zip | |
2022-07-25 16:52 | elong | Target Version | 3.14.0 => 3.14.1 |
2022-08-24 18:56 | elong | Target Version | 3.14.1 => 3.14.2 |
2022-10-03 14:38 | elong | Target Version | 3.14.2 => |