Delphix Products

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Hey Mouhssine,

I got the solution. It was a type cast error. tried using cast function in custom sql filter and that worked for me.
:)



------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 08:15:44 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Hi,

Great news, so finally it was related to data type :)

For information you casted from what to what ?

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 08:15:44 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

integer to Varchar

------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 08:35:11 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Great news, so finally it was related to data type :)

For information you casted from what to what ?

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:15:44 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

Now the same algorithm is throwing error with a similar integer column in postgres DB (only difference is that of primary key).
This column Build_Info is a primary key  and Index column.

I just wanted to replace 23649 by -23649

Struggling to resolve this from morning. Below is the error logs:

2019/12/17 08:10:43 - Update.0 - Error updating batch
2019/12/17 08:10:43 - Update.0 - Batch entry 0 UPDATE "azuredevops_reporting"."build_info"
2019/12/17 08:10:43 - Update.0 - SET "build_id" = -23649.0
2019/12/17 08:10:43 - Update.0 - WHERE ( ( "build_id" = '23649' ) ) was aborted: ERROR: operator does not exist: integer = character varying
2019/12/17 08:10:43 - Update.0 - Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

------------------------------
indrajeet yadav
Technical Test Lead
Delphix Community Members
------------------------------

Original Message:
Sent: 12-16-2019 08:51:51 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

integer to Varchar

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:35:11 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Great news, so finally it was related to data type :)

For information you casted from what to what ?

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:15:44 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.

2019/12/16 06:58:10 - Get All Lookups Values.0 - A String type was specified for field [Col0_lkUpKey], but a [class java.lang.Long] type was returned as a result of formula [release_id]

Can some one suggest me the possible resolution here:
In Oracle I was using data type as Varchar for the particular field while in Postgres DB I was using data type as int.
That is the only difference I understood.

My data cleansing file looks like:

1=-1
2=-2
3=-3
4=-4
.
.
some 1 lakh rows .


------------------------------
indrajeet yadav
Delphix Community Members
------------------------------

I am already using cast function in custom SQL while fetching data from this table.
Ex: SELECT cast ("build_id" as varchar) from <tablename>

------------------------------
indrajeet yadav
Technical Test Lead
Delphix Community Members
------------------------------

Original Message:
Sent: 12-17-2019 06:13:48 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Now the same algorithm is throwing error with a similar integer column in postgres DB (only difference is that of primary key).
This column Build_Info is a primary key  and Index column.

I just wanted to replace 23649 by -23649

Struggling to resolve this from morning. Below is the error logs:

2019/12/17 08:10:43 - Update.0 - Error updating batch
2019/12/17 08:10:43 - Update.0 - Batch entry 0 UPDATE "azuredevops_reporting"."build_info"
2019/12/17 08:10:43 - Update.0 - SET "build_id" = -23649.0
2019/12/17 08:10:43 - Update.0 - WHERE ( ( "build_id" = '23649' ) ) was aborted: ERROR: operator does not exist: integer = character varying
2019/12/17 08:10:43 - Update.0 - Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

------------------------------
indrajeet yadav
Technical Test Lead
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:51:51 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

integer to Varchar

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:35:11 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Great news, so finally it was related to data type :)

For information you casted from what to what ?

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 08:15:44 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi,

Either, try to use segment mapping algorithm or engage with support as they have the tools to look on what's going wrong on masking engine.

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:48:18 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

In postgres database Integer datatype ( INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
So the range should not be an issue as far as i understood.

------------------------------
indrajeet yadav
Delphix Community Members

Original Message:
Sent: 12-16-2019 07:14:06 AM
From: Mouhssine SAIDI
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

Hi indrajeet,

My interpretation to this error message is.

Lookup value returned is of type long that doens't fit into postgres int type, you can try to change the column type to bigint if you can or change the masking algorithm to use one that gives you more control like "segment mapping".

Regards,

Mouhssine

------------------------------
Mouhssine SAIDI
Community Member
Delphix Community Members

Original Message:
Sent: 12-16-2019 04:25:05 AM
From: indrajeet yadav
Subject: Delphix data cleansing algorithm throwing error with Postgres DB

I was replacing the positive integer with negative integer by using data cleansing algorithm.
This was working correctly in Oracle DB but same algorithm throwing error in postgres DB.
below is the error screenshot.