How do you choose the right SQL data type for a column (DECIMAL vs FLOAT, VARCHAR vs TEXT, dates, UUID, JSON, boolean)?
How do you choose the right SQL data type for a column (DECIMAL vs FLOAT, VARCHAR vs TEXT, dates, UUID, JSON, boolean)?
Choose the smallest type that represents the data exactly. The type controls storage size, validation, comparison rules and index size.
INT / BIGINT for counts and IDs. A signed 4-byte INT tops out near 2.1 billion, so use BIGINT for tables that may grow past that.DECIMAL(p,s) / NUMERIC for money and anything that must be exact.FLOAT / REAL / DOUBLE PRECISION are binary approximations, fine for measurements, wrong for money.-- PostgreSQL: binary floating point cannot represent 0.1 exactly
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8; -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; -- true
Another safe option is integers in the smallest currency unit (cents).
VARCHAR(n) when a real maximum exists (country code, email). TEXT (or VARCHAR(MAX) in SQL Server) for free text.Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.