I tried to change precision like this:
ALTER Table account_invoice ALTER amount_total SET NUMERIC(5);
But I get syntax error, so I'm clearly doing something wrong. What is the right syntax to change precision of numeric in PostgreSQL?
3 Answers
Try this:
ALTER Table account_invoice ALTER COLUMN amount_total TYPE DECIMAL(10,5);
DECIMAL(X, Y) -> X represents full length and Y represents precision of the number.
4 Comments
Andrius
Just terms are a bit confusing in PostgreSQL here: postgresql.org/docs/9.1/static/datatype-numeric.html. As precision here is called full length of numbers. Also why DECIMAL not NUMERIC?
huzeyfe
they are not different in postgresql you could use numeric as well. it maybe better indeed.
88weighed
No need to change the data type to DECIMAL. This can also be done with data type NUMERIC as answered by Maike Mota.
Max
Confirming Andrius' comment, I believe the X,Y in this answer are switched. X is precision and Y is Scale - postgresql.org/docs/14/datatype-numeric.html#id-1.5.7.9.7.4 (link to exact paragraph)
You can use this:
ALTER Table account_invoice ALTER amount_total SET DATA TYPE NUMERIC(5,Y);
where Y is your required level of precision.
3 Comments
PRASANNA SARAF
FYI: 5 is precision and Y is scale
Maike Mota
I can see the confusion thats my comment about the Y's value can cause, when I said "precision" I wanted to mean the amount of decimal places, the POSTGRE documentation refers to precision as the total number of digits that a number could contains and the scale as the total of digits reserved to be fractional (AKA. decimal places). Thanks for bring my attention to this!
William Ardila
NUMERIC(precision, scale). The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero. source
You need to ue the TYPE keyword after the column name, not SET
ALTER Table account_invoice ALTER amount_total TYPE NUMERIC(5);
See the docs: ALTER TABLE
alter table account_invoice alter column amount_type type decimal(5,0);? or like thisALTER TABLE acc_inv MODIFY amount_total NUMERIC(5,0); ?