Numeric and decimal precision in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NUMERIC(p, s) data type represent in PostgreSQL?

It represents a number with p total digits (precision) and s digits after the decimal point (scale).

beginner

What happens if you insert a number with more decimal places than the scale defined in NUMERIC(p, s)?

PostgreSQL rounds the number to fit the defined scale (number of decimal places).

beginner

How does DECIMAL differ from NUMERIC in PostgreSQL?

In PostgreSQL, DECIMAL is just an alias for NUMERIC. Both behave the same way.

intermediate

What is the default precision and scale if you declare a column as NUMERIC without parameters?

It allows any number of digits with no fixed precision or scale, limited only by the system's maximum storage.

intermediate

Why use NUMERIC or DECIMAL instead of FLOAT for money values?

Because NUMERIC stores exact decimal values without rounding errors, which is important for money calculations.

What does the scale in NUMERIC(p, s) specify?

A Number of digits before the decimal point

B Total number of digits

C Maximum value allowed

D Number of digits after the decimal point

The scale s specifies how many digits appear after the decimal point.

If you define a column as NUMERIC(5, 2), what is the largest number you can store?

A 999.99

B 99999

C 9999.9

D 99.999

With precision 5 and scale 2, you have 3 digits before and 2 digits after the decimal point, so max is 999.99.

What happens if you insert 12.345 into a NUMERIC(5, 2) column?

A It stores 12.345 exactly

B It stores 12.34 without rounding

C It stores 12.35 after rounding

D It causes an error

The value is rounded to 2 decimal places, so 12.345 becomes 12.35.

Which data type is best for storing exact monetary values?

A INTEGER

B NUMERIC

C FLOAT

D TEXT

NUMERIC stores exact decimal values, avoiding rounding errors common with FLOAT.

What is the difference between DECIMAL and NUMERIC in PostgreSQL?

A No difference, they are synonyms

B DECIMAL is faster

C NUMERIC has fixed precision, DECIMAL does not

D DECIMAL stores integers only

In PostgreSQL, DECIMAL and NUMERIC are the same data type.

Explain how precision and scale work in the NUMERIC(p, s) data type.

Think about total digits and decimal places separately.

Why is NUMERIC preferred over FLOAT for financial data?

Consider the importance of exactness in money.