POSTGRES中numeric(9,0)与int的区别、存储字节数及脚本问题咨询

Hey there! Switching from auto-ddl to Liquibase means you’ve got to be precise with your data types—let’s break down your questions so you can build accurate db-changelogs without headaches.

1. Core Differences Between numeric(9,0) and INT #

Even though both can store whole numbers, they’re totally distinct types in Postgres:

• INT (integer) is a native fixed-size integer type. It’s part of Postgres’ optimized integer family (alongside smallint and bigint) and uses hardware-accelerated operations, making it fast for calculations.
• numeric(9,0) falls into the arbitrary-precision numeric category. Even with a scale of 0 (no decimals), it retains the "exact decimal" behavior of the numeric type—great for scenarios where precision is non-negotiable (like financial data), but operations are handled via software, so they’re slower than INT.

Other key distinctions:

• Range Limits: INT can hold values from -2147483648 to 2147483647 (that’s a 10-digit max). numeric(9,0) is strictly capped at -999999999 to 999999999 (9 digits flat). So INT has a wider valid range.
• Error Behavior: If you try to insert a value outside the type’s range, INT might wrap around (if you’ve disabled strict checks, which isn’t recommended) or throw an error. numeric(9,0) will always reject out-of-range values immediately, since it enforces the precision you defined.

2. Are numeric(9,0) and INT Equivalent? Storage Breakdown #

Short answer: No, they’re not equivalent. Postgres treats them as separate types, even if you’re only storing 9-digit integers.

Storage Size Details: #

• INT: Uses a fixed 4 bytes for every valid value—no variability here.
• numeric(9,0): Uses variable storage, but for 9-digit integers, it’s 6 bytes total (2 bytes for a header tracking precision/scale/sign, plus 4 bytes to store the 9-digit group). That’s 50% more storage than INT for the same range of values.

Critical Tip for Your Liquibase Migration #

Since you’re moving from auto-ddl (which might have auto-selected types) to explicit Liquibase changelogs, make sure you mirror the exact type from your existing schema. If auto-ddl created INT columns, stick with INT in your changelogs—don’t switch to numeric(9,0) unless you intentionally want to restrict the value range. Conversely, if your schema uses numeric(9,0), don’t swap to INT unless you’re sure all values fit within INT’s range (and you want the performance boost). Mismatching types could lead to data truncation, unexpected errors, or unnecessary performance hits.

内容的提问来源于stack exchange，提问作者JITHIN_PATHROSE