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
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);
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)
select '9_995_959'::numeric; 9995959.
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.9.995.959started out as9,995,959then 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?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')returns9123456, losing the789at the end: demo.