0

so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while researching it that numeric is better than float).

I'm trying to run this command:

ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0)
USING prevailing_wage::numeric;

But I'm getting the error

ERROR: invalid input syntax for type numeric: "40768,43"

I'm not sure how I can make it work. Any idea?

1
  • Some sample values and your version of Postgres would help. Also, "string" is not a Postgres data type. Is it text or varchar or something else? May 16, 2015 at 2:50

2 Answers 2

4

I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.

1
0

Replace the comma with a dot, which is the default "decimal point".

ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0)
USING translate(prevailing_wage, ',', '.')::numeric;

But as long as you are not going to store fractional digits (numeric(10,0)), you should really use integer (covers -2147483648 to +2147483647 or bigint (covers -9223372036854775808 to +9223372036854775807).

Or you really mean numeric(10,2) instead of numeric(10,0)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.