What is the difference between numeric(9,0) and int in POSTGRES?

Question

Are NUMERIC(9,0) and INT the same in Postgres?

How many bytes of data can each type store?

Background: I was using the auto-ddl API to apply scripts to a database while starting a Spring container. Now I am trying to generate the scripts using liquibase by generating the db-changelog for POSTGRES server.

Answer 1

NUMERIC(9, 0) and INT are different types in Postgres. The important difference is in storage format.

The numeric type can be between 0 and 255 bytes, as needed. The type modifier (9, 0) has no impact on the format of saved data, it is just the limit of the maximum length.

The INT type has a fixed length 4 bytes.

The size of '999999999'::numeric:

postgres=# select pg_column_size('999999999'::numeric);
+----------------+
| pg_column_size |
+----------------+
|             12 |
+----------------+
(1 row)

NB: some operations on NUMERIC types are slower than on INT types. The NUMERIC type needs more space; it is designed for working with large numbers, or precise numbers. It is not designed as a replacement for the INT type.

Postgres does not translate NUMERIC(x, 0) to INT or BIGINT.

Answer 2

No, they are not the same.

INT is a 4-byte column storing integer values from -2147483648 to +2147483647.

NUMERIC(9, 0) is a 9- to 14-byte column storing integer values from -999999999 to 999999999.