PostgreSQL Data Types and Schemas

---

Choosing the Right Data Type

Data types are more than just technical details—they enforce business rules, optimize storage, and improve query performance. Choosing wisely from the start prevents headaches later.

---

Core Data Types

Text Types

-- Fixed-length (padded with spaces)
char(10)        -- Always 10 characters

-- Variable-length with limit
varchar(255)    -- Up to 255 characters

-- Unlimited text
text            -- Any length, most flexible

Recommendation: Use text for most string data in Supabase. PostgreSQL handles it efficiently, and artificial limits often cause problems later.

Numeric Types

-- Integers
smallint        -- -32,768 to 32,767
integer         -- -2 billion to 2 billion
bigint          -- Very large numbers

-- Auto-incrementing (legacy)
serial          -- Auto-increment integer
bigserial       -- Auto-increment bigint

-- Exact decimals (for money!)
numeric(10, 2)  -- 10 digits, 2 after decimal
decimal(10, 2)  -- Same as numeric

-- Floating point (approximate)
real            -- 6 decimal precision
double precision -- 15 decimal precision

Warning: Never use floating point for money!

-- Wrong: floating point errors
SELECT 0.1 + 0.2;  -- Might be 0.30000000000000004

-- Right: use numeric or store as cents
amount numeric(10, 2)  -- or
amount_cents integer   -- Store $10.50 as 1050

Boolean

is_active boolean DEFAULT true
is_published boolean NOT NULL DEFAULT false

Values: true, false, or null (if nullable)

Date and Time

-- Date only
birth_date date  -- '2024-01-15'

-- Time only (rare)
opens_at time    -- '09:00:00'

-- Timestamp WITHOUT timezone (avoid this)
created_at timestamp  -- '2024-01-15 10:30:00'

-- Timestamp WITH timezone (always use this!)
created_at timestamptz  -- '2024-01-15 10:30:00+00'

Best Practice: Always use timestamptz for timestamps:

-- Good: timezone-aware
created_at timestamptz DEFAULT now()

-- Bad: ambiguous timezone
created_at timestamp DEFAULT now()

---

Supabase-Specific Types

UUIDs

The standard identifier type in Supabase:

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  -- or
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

Why UUIDs over integers?

Aspect	UUID	Serial Integer
Globally unique	Yes	No
Guessable	No	Yes (security risk)
Distributed generation	Yes	Needs coordination
Storage size	16 bytes	4-8 bytes
Human readable	No	Yes

For Supabase applications, UUIDs are preferred because:

• They match auth.users.id type
• Safe to expose in URLs
• Work across distributed systems

JSON Types

-- JSON (stored as text, validated)
metadata json

-- JSONB (binary, faster queries, recommended)
settings jsonb DEFAULT '{}'::jsonb

JSONB Operations:

-- Create
INSERT INTO users (settings)
VALUES ('{"theme": "dark", "notifications": true}'::jsonb);

-- Access
SELECT settings->>'theme' as theme FROM users;
SELECT settings->'notifications' as notifications FROM users;

-- Query
SELECT * FROM users
WHERE settings @> '{"theme": "dark"}';

-- Update
UPDATE users
SET settings = settings || '{"language": "en"}'::jsonb
WHERE id = '...';

When to use JSONB:

• User preferences
• Flexible metadata
• External API data
• Schema-less portions of otherwise structured data

When NOT to use JSONB:

• Core business data (use proper columns)
• Data you query frequently (columns are faster)
• When relationships matter (can't foreign key into JSON)

Arrays

-- Array columns
tags text[]
scores integer[]

-- Usage
INSERT INTO posts (tags)
VALUES (ARRAY['tech', 'tutorial', 'postgres']);

-- Query: contains
SELECT * FROM posts WHERE 'tech' = ANY(tags);

-- Query: overlap
SELECT * FROM posts WHERE tags && ARRAY['tech', 'news'];

---

Understanding Schemas

Schemas are namespaces for database objects. Think of them as folders for organizing tables, views, and functions.

Default Supabase Schemas

-- Your application data
public        -- Tables you create go here by default

-- Managed by Supabase
auth          -- User accounts, sessions
storage       -- File metadata, buckets
extensions    -- PostgreSQL extensions
realtime      -- Realtime configuration

-- System
pg_catalog    -- PostgreSQL system tables
information_schema  -- Standard metadata

Working with Schemas

-- Create a table in the public schema (default)
CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

-- Explicit schema reference
CREATE TABLE public.posts (...);

-- Create a custom schema
CREATE SCHEMA private;

-- Create table in custom schema
CREATE TABLE private.admin_settings (
  key text PRIMARY KEY,
  value jsonb
);

Schema Search Path

PostgreSQL looks for tables in a search path:

-- See current search path
SHOW search_path;
-- Typically: "$user", public

-- When you query 'posts', PostgreSQL checks:
-- 1. $user.posts (rarely exists)
-- 2. public.posts (usually found here)

Referencing Auth Tables

The auth schema contains user data:

-- Reference auth.users in your tables
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name text,
  avatar_url text
);

-- Query with join
SELECT
  p.*,
  u.email
FROM profiles p
JOIN auth.users u ON p.id = u.id;

---

Nullability and Defaults

NOT NULL Constraint

CREATE TABLE posts (
  id uuid PRIMARY KEY,
  title text NOT NULL,           -- Required
  content text,                  -- Optional (nullable)
  views integer NOT NULL DEFAULT 0
);

Default Values

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  status text DEFAULT 'draft',
  metadata jsonb DEFAULT '{}'::jsonb
);

Generated Columns

Automatically computed values:

CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  price numeric NOT NULL,
  quantity integer NOT NULL,
  total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

---

Common Patterns

User-Owned Resources

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  title text NOT NULL,
  content text,
  created_at timestamptz DEFAULT now() NOT NULL,
  updated_at timestamptz DEFAULT now() NOT NULL
);

-- Index for user queries
CREATE INDEX idx_posts_user_id ON posts(user_id);

Enumerated Status

-- Option 1: Check constraint
status text CHECK (status IN ('draft', 'published', 'archived'))

-- Option 2: Enum type (harder to modify)
CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');
status post_status DEFAULT 'draft'

-- Option 3: Reference table (most flexible)
CREATE TABLE post_statuses (
  id text PRIMARY KEY  -- 'draft', 'published', etc.
);
status text REFERENCES post_statuses(id)

Recommendation: Use check constraints for simple cases, reference tables for complex or frequently changing enums.

Timestamps Pattern

CREATE TABLE posts (
  -- ...
  created_at timestamptz DEFAULT now() NOT NULL,
  updated_at timestamptz DEFAULT now() NOT NULL,
  deleted_at timestamptz  -- For soft deletes
);

-- Auto-update updated_at with trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

---

Type Conversion

Casting

-- Explicit cast
SELECT '123'::integer;
SELECT 123::text;
SELECT now()::date;

-- Cast in queries
SELECT * FROM posts WHERE id = 'some-uuid'::uuid;

Common Conversions

-- Text to number
SELECT '42'::integer;
SELECT '3.14'::numeric;

-- Number to text
SELECT 42::text;

-- Timestamp operations
SELECT now()::date;  -- Just the date
SELECT now()::time;  -- Just the time
SELECT '2024-01-15'::timestamptz;  -- Text to timestamp

---

Key Takeaways

1. Use text freely: PostgreSQL handles it efficiently
2. Always use timestamptz: Avoid timezone ambiguity
3. Prefer UUIDs: They're the Supabase standard
4. JSONB for flexibility: But not for core data
5. Reference auth.users: For user-related data
6. Default values reduce errors: Set sensible defaults
7. NOT NULL when required: Enforce at the database level

---

Next Steps

With data types understood, we'll explore how to model relationships between tables using foreign keys and best practices for data integrity.

---

The right data type is documentation that the database enforces. When someone asks "what's valid here?", the schema answers.