claude-skills/supabase-postgres-best-practices/references/schema-data-types.md

title, impact, impactDescription, tags

title	impact	impactDescription	tags
Choose Appropriate Data Types	HIGH	50% storage reduction, faster comparisons	data-types, schema, storage, performance

Choose Appropriate Data Types

Using the right data types reduces storage, improves query performance, and prevents bugs.

Incorrect (wrong data types):

create table users (
  id int,                    -- Will overflow at 2.1 billion
  email varchar(255),        -- Unnecessary length limit
  created_at timestamp,      -- Missing timezone info
  is_active varchar(5),      -- String for boolean
  price varchar(20)          -- String for numeric
);

Correct (appropriate data types):

create table users (
  id bigint generated always as identity primary key,  -- 9 quintillion max
  email text,                     -- No artificial limit, same performance as varchar
  created_at timestamptz,         -- Always store timezone-aware timestamps
  is_active boolean default true, -- 1 byte vs variable string length
  price numeric(10,2)             -- Exact decimal arithmetic
);

Key guidelines:

-- IDs: use bigint, not int (future-proofing)
-- Strings: use text, not varchar(n) unless constraint needed
-- Time: use timestamptz, not timestamp
-- Money: use numeric, not float (precision matters)
-- Enums: use text with check constraint or create enum type

Reference: Data Types