Problem in converting the datatype of a column in PostgreSQL

Question

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

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

Zegarek · Accepted Answer · 2025-03-07 11:06:45Z

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)

... your string must be 9995959. is not strictly true. In Postgres 16+ you can do: select '9_995_959'::numeric; 9995959. — Adrian Klaver, Commented Mar 6 at 17:45

Collectives™ on Stack Overflow

Problem in converting the datatype of a column in PostgreSQL

1 Answer 1

If you have decimals too

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

If you have decimals too

Related