$ cat /posts/supabase-database-postgresql-basics-and-table-creation.md

Supabase Database: PostgreSQL Basics and Table Creation

drwxr-xr-x2026-01-255 min0 views

Supabase provides a full-featured PostgreSQL 15 database with every project, giving you access to one of the most advanced open-source relational databases with 35+ years of development and optimization. Understanding PostgreSQL fundamentals and proper table design is crucial for building scalable, maintainable applications with data integrity and performance. This comprehensive guide covers PostgreSQL data types, creating tables with the Supabase dashboard and SQL, defining constraints for data validation, understanding primary and foreign keys, and best practices for schema design. Whether you're new to SQL or experienced with databases, mastering these fundamentals ensures your Supabase applications have solid data foundations. Before proceeding, complete Supabase installation and review architecture basics.

Why PostgreSQL?

PostgreSQL excels over NoSQL databases like Firestore with ACID compliance ensuring data integrity during transactions, support for complex relationships through foreign keys, powerful querying with JOINs and subqueries, 100+ extensions for specialized features, and mature ecosystem with extensive tooling. Unlike document databases requiring denormalization, PostgreSQL maintains normalized data reducing redundancy and update anomalies.

PostgreSQL Data Types

Data Type	Description	Example	Use Case
INTEGER	Whole numbers	42, -10, 0	IDs, counts, quantities
BIGINT	Large integers	9223372036854775807	Large IDs, timestamps
TEXT	Variable-length text	'Hello World'	Names, descriptions, content
VARCHAR(n)	Limited-length text	'[email protected]'	Emails, usernames (with limit)
BOOLEAN	True/false values	true, false	Flags, is_active, is_verified
TIMESTAMP	Date and time	'2026-01-25 12:00:00'	created_at, updated_at
UUID	Unique identifier	'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'	Primary keys, secure IDs
JSONB	Binary JSON data	'{"key": "value"}'	Flexible fields, metadata
NUMERIC	Precise decimals	99.99, 0.001	Prices, percentages

Creating Your First Table

Supabase provides two methods for creating tables: the visual Table Editor for beginners, and SQL Editor for advanced users. The Table Editor offers a user-friendly interface with dropdown menus for data types, checkbox options for constraints, and automatic SQL generation. SQL provides more control for complex schemas with triggers, indexes, and custom constraints.

sqlcreate_users_table.sql

-- Create a 'users' table with PostgreSQL SQL

create table users (
  id uuid default gen_random_uuid() primary key,
  email text unique not null,
  username text unique not null,
  full_name text,
  avatar_url text,
  bio text,
  is_active boolean default true,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Add comments for documentation
comment on table users is 'User accounts and profiles';
comment on column users.email is 'User email address (unique)';

-- Create an index for faster lookups
create index users_email_idx on users(email);
create index users_username_idx on users(username);

-- Create a trigger to auto-update 'updated_at'
create or replace function update_updated_at_column()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger update_users_updated_at
  before update on users
  for each row
  execute function update_updated_at_column();

Table Constraints

PRIMARY KEY: Uniquely identifies each row. Use UUID for distributed systems or SERIAL for simple auto-incrementing IDs
UNIQUE: Ensures column values are unique across all rows. Perfect for emails, usernames, or slugs
NOT NULL: Prevents null values ensuring data completeness. Use for required fields like email or created_at
DEFAULT: Provides default values when not specified. Common for timestamps (now()) or booleans (false)
CHECK: Validates data against conditions. Example: CHECK (age >= 18) for adult users
FOREIGN KEY: Maintains referential integrity between tables. Links related data ensuring consistency

Defining Relationships

sqlcreate_relationships.sql

-- Create 'posts' table with foreign key to 'users'

create table posts (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references users(id) on delete cascade not null,
  title text not null,
  content text not null,
  status text default 'draft' check (status in ('draft', 'published', 'archived')),
  view_count integer default 0,
  created_at timestamp with time zone default now() not null,
  updated_at timestamp with time zone default now() not null
);

-- Create index on foreign key for faster joins
create index posts_user_id_idx on posts(user_id);

-- Create 'comments' table (many-to-one relationship)
create table comments (
  id uuid default gen_random_uuid() primary key,
  post_id uuid references posts(id) on delete cascade not null,
  user_id uuid references users(id) on delete cascade not null,
  content text not null,
  created_at timestamp with time zone default now() not null
);

create index comments_post_id_idx on comments(post_id);
create index comments_user_id_idx on comments(user_id);

Foreign keys ensure data integrity by preventing orphaned records. The 'on delete cascade' option automatically deletes related records when the parent is deleted. Learn advanced relationship patterns in our relationships and joins guide.

Database Design Best Practices

Use UUID for Primary Keys: Better for distributed systems, security (non-sequential), and avoiding ID conflicts during migrations
Always Add Timestamps: Include created_at and updated_at for audit trails and debugging
Enforce NOT NULL: Mark required fields as NOT NULL to prevent incomplete data
Create Indexes: Add indexes to foreign keys and frequently queried columns for performance
Use Meaningful Names: Choose clear, descriptive table and column names following conventions (snake_case)
Document with Comments: Add SQL comments explaining table purposes and complex columns

Using Supabase Table Editor

Navigate to your Supabase project dashboard and select Table Editor from the sidebar. Click 'New Table' to create a table visually. Provide a table name (lowercase, snake_case), enable Row Level Security (recommended), and add columns with appropriate data types. Set primary key (usually id with UUID type), configure constraints (unique, not null), and set default values. The Table Editor generates SQL automatically, showing you the underlying code for learning.

Database Migrations

For production applications, use database migrations to track schema changes with version control, enable team collaboration on database structure, and deploy consistent schemas across environments. Supabase CLI provides migration tools creating timestamped SQL files you can commit to Git. Learn comprehensive migration workflows for production apps.

Security Warning: Always enable Row Level Security (RLS) on your tables! Without RLS, all data is publicly accessible. Learn to implement Row Level Security policies immediately after creating tables.

Next Steps

Practice Creating Tables: Build a simple schema with 3-4 related tables to understand relationships
Learn SQL Queries: Master SELECT, INSERT, UPDATE, DELETE operations in our queries guide
Implement RLS Policies: Secure your data with Row Level Security for multi-tenant apps
Add Triggers and Functions: Automate database logic with PostgreSQL triggers
Optimize with Indexes: Improve query performance by creating appropriate indexes on frequently queried columns

Conclusion

Understanding PostgreSQL fundamentals and proper table design forms the foundation of every successful Supabase application. By leveraging appropriate data types, constraints, and relationships, you ensure data integrity, optimize performance, and maintain scalable schemas. The combination of Supabase's visual Table Editor for rapid prototyping and SQL Editor for advanced control provides flexibility for developers at all skill levels. Always remember to enable Row Level Security, use meaningful naming conventions, create appropriate indexes, and document your schema with comments. With solid database fundamentals mastered, you're ready to build robust applications with complex data models and relationships. Continue your learning journey with database queries, relationships and joins, and Row Level Security to complete your database expertise.