View Issue Details

IDProjectCategoryView StatusLast Update
0005354SymmetricDSBugpublic2022-10-03 14:38
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status assignedResolutionopen 
Product Version3.9.13 
Summary0005354: Sybase ASE Small Int not able to be converted to Postgres boolean without transformation or casting
DescriptionWe 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 InformationThis 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
TagsNo tags attached.

Activities

pmarzullo

2022-07-11 15:18

developer  

Issue History

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 =>