Money fields throwing errors - Bad value for type double : 10,000,000.00

[Daemach]

Great script, and thanks for your work on this. It will speed up Postgres adoption.

This happens to be an mssql money field, but it will affect smallmoney too. We use money type a lot, and there seems to be no way to get around this problem in the Docker container I am building. Mapping mssql money to Postgres double and including commas isn't functional.

SELECT * FROM "vadmin_dbo"."accounts" LIMIT 1
Bad value for type double : 10,000,000.00

I've tried adding a neutral locale to the entry point script, but it's not solving the problem. Any ideas?

export LOCALE=C
export LANG=C

cat <<'EOF' > /usr/local/etc/locales.conf
[default]
date format = %b %e %Y %I:%M:%S.%z%p

[en_US]
date format = %b %e %Y %I:%M:%S.%z%p
language = us_english
charset = iso_1

[es_ES]
date format = %b %d %Y %I:%M%p
language = spanish
charset = iso_1

[pt_BR]
date format = %d/%m/%Y %H:%M
language = Portuguese
charset = iso_1

[it_IT]
date format = %d/%m/%Y %H:%M
language = Italiano
charset = iso_1

[C]
language = us_english
charset = iso_1
date format = %Y-%m-%d %H:%M:%S
decimal_sep = .
thousand_sep =
grouping = 0
mon_decimal_sep = .
mon_thousand_sep =
mon_grouping = 0
currency_symbol =
p_cs_precedes = 0
p_sep_by_space = 0
p_sign_posn = 3
n_cs_precedes = 0
n_sep_by_space = 0
n_sign_posn = 3
EOF

su postgres -c "psql -d ${POSTGRES_DB} -c "
CREATE SERVER IF NOT EXISTS ${server_name}
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (
servername '${db_host}',
port '${db_port}',
database '${db_database}',
language 'us_english',
tds_version '7.4'
);""

[Daemach]

Also, please update the us_english date pattern to %b %e %Y %I:%M:%S.%z%p or timestamps fail.

[freddy77]

Can you describe a bit more the setup ? I mean, how are you connecting Postgres and MSSQL ? I suppose "vadmin_dbo"."accounts" is a linked table with a money field. Not sure where the 10,000,000.00 value came (especially with that format). Do you have idea what destination type a money value is going to end up in Postgres ?