PostgreSQL - change precision of numeric?

Question

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?

`alter table account_invoice alter column amount_type type decimal(5,0);` ? or like this `ALTER TABLE acc_inv MODIFY amount_total NUMERIC(5,0); ?` — vhadalgi, Feb 12 '14 at 11:20
The right syntax is documented: http://www.postgresql.org/docs/current/static/sql-altertable.html — , Feb 12 '14 at 11:27

score 41 · Accepted Answer · answered Feb 12 '14 at 11:22

41

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.

answered Feb 12 '14 at 11:22

huzeyfe

2

Just terms are a bit confusing in PostgreSQL here: http://www.postgresql.org/docs/9.1/static/datatype-numeric.html. As precision here is called full length of numbers. Also why DECIMAL not NUMERIC? – Andrius Feb 12 '14 at 12:06
they are not different in postgresql you could use numeric as well. it maybe better indeed. – huzeyfe Feb 13 '14 at 14:57
1

No need to change the data type to DECIMAL. This can also be done with data type NUMERIC as answered by Maike Mota. – 88weighed Nov 22 '18 at 13:06
Confirming Andrius' comment, I believe the X,Y in this answer are switched. X is precision and Y is Scale - https://www.postgresql.org/docs/14/datatype-numeric.html#id-1.5.7.9.7.4 (link to exact paragraph) – Max Jan 05 '22 at 01:48

Maike Mota · Answer 2 · 2015-10-04T08:51:44.607

15

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.

edited Oct 04 '15 at 08:51

answered Oct 02 '15 at 11:25

Maike Mota

FYI: 5 is precision and Y is scale – PRASANNA SARAF Feb 11 '19 at 08:04
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! – Maike Mota Feb 11 '19 at 18:58
2

**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](https://www.postgresql.org/docs/9.1/datatype-numeric.html) – William Ardila Nov 20 '20 at 20:35

score 7 · Answer 3 · answered Feb 12 '14 at 11:20

7

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

answered Feb 12 '14 at 11:20

GarethD

3 Answers3