Type money

[sphyr]

A select on a foreign table with money column gave me this error :

ERROR: invalid input syntax for type money: "0.00"

What's happened ?

Best regards

[Smtgr14]

Please post the query you are running and the Foreign Table definition.

[sphyr]

Here is the foreign table definition :

         Foreign Table « dbo.testmoney »

Colonne | Type | Modificateurs | Options FDW
---------+---------+---------------+----------------------- id | integer | | (column_name 'id') price | money | | (column_name 'price') Server: mssql FDW Options: (schema_name 'dbo', table_name 'testmoney')

The query was a simple :

select * from testmoney;

[sphyr]

Sorry for the formatting

Best regards

[Smtgr14]

Can you also provide the definition of the mssql table?

[sphyr]

Here is the create of the table testmoney

create table testmoney ( id int null, price money null)

[Smtgr14]

Interesting. I've constructed a set of tables based on your definitions and it functions properly. Paste the definition of the server mssql. But first, can you try this foreign table definition:

CREATE FOREIGN TABLE testmoney2 (
  "id" int,
  price money
) SERVER mssql
OPTIONS (query 'SELECT id, price FROM testmoney', row_estimate_method 'execute');

I'm defining the table with implicit column name matching and using a query (written in T-SQL) to retrieve table details.

[sphyr]

Here's the result of the 'select * from testmoney2' :

select * from testmoney2;

NOTICE: tds_fdw: Query executed correctly NOTICE: tds_fdw: Getting results ERROR: invalid input syntax for type money : « 20.00 »

[sphyr]

The problem is fixed !

The data in the table and de lc_monetary mismatch. When i've set lc_monetary to 'en_US.UTF-8' the query ran nicely.

Sorry for disturbing you ! Have a nice evening.

[Smtgr14]

Glad it works. Can you be a sweetie and close the issue?

[sphyr]

Thank you for your help.