I created a whole text table to avoid problems in the import of the csv. After that I changed the data cleanup to "," in "." as number dividers to avoid problems with the delimiter.

But when I go to do ALTER TABLE E ALTER COLUMN I get the error that the format "9.995.959" is not compatible with numeric

I created a whole text table to avoid problems in the import of the csv. After that I changed the data cleanup to "," in "." as number dividers to avoid problems with the delimiter.

But when I go to do ALTER TABLE E ALTER COLUMN I get the error that the format "9.995.959" is not compatible with numeric

• sql
• postgresql
• csv
• import
• numeric

• 5 And that is correct: select '9.995.959'::numeric; ERROR: invalid input syntax for type numeric: "9.995.959". There cannot be two decimal places. You need to add to your question the data you started with and the code you used to change it. – Adrian Klaver Commented Mar 6 at 17:12
• Assuming 9.995.959 started out as 9,995,959 then you would need to do something like: select to_number('9.995.959', '999G999G999'); 9995959. For more information see Data formatting functions in particular Table 9.29. Template Patterns for Numeric Formatting. Also what version of Postgres are you using? – Adrian Klaver Commented Mar 6 at 17:24
• to_number()'s nice but if the mask happens to be shorter than the input, it'll truncate it. select to_number('9.123.456.789','999G999G999') returns 9123456, losing the 789 at the end: demo. – Zegarek Commented Mar 7 at 11:19

1 Answer 1

In numeric types, there can be only one decimal separator. PostgreSQL v16+ also accepts an underscore _ as a thousands separator:

For visual grouping, underscores can be inserted between digits. These have no further effect on the value of the constant. For example:

1_500_000_000
0b10001000_00000000
0o_1_755
0xFFFF_FFFF
1.618_034

If you want to write "nine million nine hundred ninety nine thousands nine hundred fifty nine", your 9.995.959 must become a 9_995_959 or just 9995959.

Thus add an additional cleanup for the transition, with a replace() of all dots by nothing, before casting to numeric:

alter table t alter i type numeric using (replace(i, '.', '')::numeric);

And it runs.

If you have decimals too

Then ensure you didn't mix , and . to the sole . (else you'll not be able to distinguish the decimal separator, to preserve, from the thousands ones, to remove)