I hope this is the right mailing list to post questions about pgsql odbc. I am sorry if I made a mistake and it is not.
I have a Postgres database (9.1) and an Oracle database (10.2) whose character set is utf8. I installed the 64-bit 9.1 unicode psqlodbc driver along with the Oracle Database Gateway for ODBC (DG4ODBC), version 11.2 because I have the need to access data in the Postgres database from the Oracle one.
It works fine, but we have noticed the following. When a query such as this is issued in Oracle:
select * from "dps_user"@pg where "id" = '32422'
The where clause is dropped, and the query that reaches the Postgres database is:
select * from "dps_user"
So the whole table data are brought from Postgres, and then the where clause is applied in Oracle. This is pretty inefficient with big tables.
We have noticed that this only happens when the column in the where clause is of type character varying, due to the fact that the Oracle Database Gateway for ODBC returns all character varying columns in Postgres as type NVARCHAR2. If we issue the query like this, then the where clause is passed to Postgres:
select * from "dps_user"@pg where "id" = cast('32422' as nvarchar2(40))
This behavior does not happen when we used the psqlodbc ANSI driver, but unfortunately the Unicode driver is a must because both databases contain Japanese, Russian and Chinese characters. It does not happen either when we access the Postgres database through the psqlodbc Unicode driver from, for example, Microsoft Query, so it has something to do with the Oracle Database Gateway.
However, I thought I would post my problem here, just in case you ran into this problem or heard of someone who did. Having to use the cast as nvarchar2 works, but it is very cumbersome. I wonder if there would be a way to configure the psqlodbc driver so that a varchar2 column is returned instead.
I am going to open a service request with Oracle to see if they can help me on the gateway end.
Sorry again if this is not the right place for this question.
