search

wolfgangbrandl / db2_fdw

PostgreSQL DB2 Data Wrapper
Other
20 stars 13 forks source link

decimal separator #23

Open hahnn opened 4 years ago

hahnn commented 4 years ago

Hello,

I've an issue with decimal separator.

I've a DB2 (v11.5.0.0) database in UTF-8 with TERRITORY=CH.

The PostgreSQL (v12.2) database is in UTF-8 as well, with LC_COLLATE and LC_CTYPE set to 'fr_FR.UTF-8'.

I can see all data that are in the DB2 database via the DB2 FDW on PostgreSQL side, except all data that are with real, double, numeric types because the DB2 FDW generates an error when encountering such type of data from DB2:

# SELECT * FROM aff_db2.my_table LIMIT 5;
ERREUR:  syntaxe en entrée invalide pour le type real : « 1,500 »
CONTEXTE : converting column "field2" for foreign table scan of "my_table", row 1

# \d+ aff_db2.my_table
                                  Table distante « aff_db2.my_table »
     Colonne      |  Type   | Collationnement | NULL-able | Par défaut | Options FDW | Stockage | Cible de statistiques | Description
------------------+---------+-----------------+-----------+------------+-------------+----------+-----------------------+-------------
 id               | bigint  |                 | not null  |            |             | plain    |                       |
 field1           | integer |                 | not null  |            |             | plain    |                       |
 field2           | real    |                 | not null  |            |             | plain    |                       |
 field3           | real    |                 | not null  |            |             | plain    |                       |
Serveur : srv1
Options FDW : (schema 'AFF', "table" 'MY_TABLE')

I suppose PostgreSQL and/or the DB2 FDW want numeric values with a . (dot) seperator instead of a , (comma) one. I tried to set lc_numeric variable in PostgreSQL (with SET lc_numeric=en_US.UTF-8 command for example) but no success.

Do you now if there is a way to solve this kind of issue? Is there a parameter we could pass as an option to the DB2 FDW that would set the decimal separator to be used?

Thanks for your help.

omensinger commented 2 years ago

I had the same problem, solved with db2 update cli cfg for section COMMON using Patch2 15 (found it here: https://github.com/ibmdb/python-ibmdb/issues/11).