Postgres connector raises itgenrst007 with empty string

[sda]

I have a small issue with PostgreSQL on Invantive Query Tool.

I am having a table with a column where NULL value are not allowed in the datatype and some values are empty (``, not null).

It seems that the Invantive connectors translates ‘’ into null thus throwing an error itgenrest007:

Can I find a turnaround on Invantive side?

[forums]

PostgreSQL is one of the few platforms (actually, it is the only one known) that clearly implements null to be different from the empty string “”. Oracle does it somewhat, depending on the context.

Given the amount of different platforms, we have found it necessary for Invantive SQL in this case to make null and the empty string have the same semantics. Specifically on PostgreSQL this can lead to the error itgenrst007 about a missing value when inserting an empty string in a not-null column.

I am sorry, but the logic to rewrite the empty string to null is deeply integrated and there is no easy possibility to circumvent it. Although it can be an annoyance, the problem only occurs on specific scenarios on PostgreSQL and the range of issues on other platforms due to making the two values have different semantics are significantly larger.

The following workarounds might be possible outside of Invantive:

• make the column nullable;
• add a trigger on the PostgreSQL table to rewrite null into the empty PostgreSQL-string again (possible taking the client program into consideration whether to do this).

[sda]

Ok thanks.

Strange is if i do that this works:

select * except name
,      case 
       when name is null 
       then '' 
       end 
from   project_task@postgres

[forums]

That is strange indeed. It seems somehow to bypass the checks in the current release.

Maybe as an alternative: create a view in PostgreSQL that makes the changes necessary to allow handling by Invantive SQL.

[forums]

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.