CODEWITHSUNDEEP
sqlpostgresqlcsvimportnumeric
Simone Romualdi
Simone Romualdi

Reputation: 19

Problem in converting the datatype of a column in PostgreSQL

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

Upvotes: 0

Views: 66

Answers (1)

Guillaume Outters
Guillaume Outters

Reputation: 1989

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)

Upvotes: 1

Related Questions