Tables

Create and manage PostgreSQL tables in Supabase

Tables are the fundamental building blocks of relational databases. They store your data in rows and columns, similar to spreadsheets but with powerful querying and relationship capabilities.

Creating Tables

You can create tables through the Dashboard, SQL Editor, or migrations.

Using the Dashboard

1. Navigate to the Table Editor in your Supabase Dashboard
2. Click New Table
3. Enter the table name and add columns
4. Configure primary keys and constraints
5. Click Save

Using SQL

create table movies (
  id bigint generated by default as identity primary key,
  name text not null,
  description text,
  release_year integer,
  rating numeric(3,1),
  created_at timestamptz default now()
);

Using Migrations

For production workflows, use migrations to track schema changes:

# Create a migration file
supabase migration new create_movies_table

supabase/migrations/20241205075911_create_movies_table.sql

create table if not exists movies (
  id bigint generated always as identity primary key,
  name text not null,
  description text,
  release_year integer check (release_year > 1800),
  rating numeric(3,1) check (rating >= 0 and rating <= 10),
  created_at timestamptz default now()
);

Data Types

PostgreSQL supports a rich set of data types:

Common Data Types

Type	Description	Example
text	Variable-length text	'Hello World'
varchar(n)	Variable-length text with limit	'Limited'
integer / int	4-byte integer	42
bigint	8-byte integer	9223372036854775807
numeric(p,s)	Exact decimal	99.99
boolean	True or false	true
timestamptz	Timestamp with timezone	2024-03-04 10:30:00+00
uuid	Universally unique identifier	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
jsonb	Binary JSON data	{"key": "value"}

Advanced Data Types

-- Array columns
create table products (
  id bigint primary key,
  tags text[],  -- Array of text values
  prices numeric[]
);

-- JSON columns
create table users (
  id bigint primary key,
  metadata jsonb,  -- Flexible structured data
  preferences json
);

-- Custom enum types
create type user_status as enum ('active', 'inactive', 'suspended');

create table users (
  id bigint primary key,
  status user_status default 'active'
);

Primary Keys

Every table should have a primary key to uniquely identify each row.

Auto-incrementing Identity

create table employees (
  id bigint generated always as identity primary key,
  name text not null,
  email text unique
);

UUID Primary Keys

create table sessions (
  id uuid primary key default gen_random_uuid(),
  user_id bigint not null,
  created_at timestamptz default now()
);

Composite Primary Keys

create table user_roles (
  user_id bigint,
  role_id bigint,
  primary key (user_id, role_id)
);

Constraints

Constraints enforce data integrity rules.

NOT NULL Constraint

create table customers (
  id bigint primary key,
  email text not null,  -- Cannot be null
  name text not null
);

UNIQUE Constraint

create table users (
  id bigint primary key,
  username text unique,  -- Must be unique
  email text unique
);

CHECK Constraint

create table products (
  id bigint primary key,
  price numeric check (price >= 0),  -- Must be positive
  quantity integer check (quantity >= 0 and quantity <= 10000)
);

DEFAULT Values

create table orders (
  id bigint primary key,
  status text default 'pending',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

Foreign Keys & Relationships

Foreign keys create relationships between tables.

One-to-Many Relationship

-- Create the parent table
create table categories (
  id bigint generated always as identity primary key,
  name text not null
);

-- Create the child table with foreign key
create table products (
  id bigint generated always as identity primary key,
  name text not null,
  category_id bigint references categories(id) on delete cascade
);

Many-to-Many Relationship

-- Create the main tables
create table movies (
  id bigint generated by default as identity primary key,
  name text not null
);

create table actors (
  id bigint generated by default as identity primary key,
  name text not null
);

-- Create a junction table
create table movie_actors (
  movie_id bigint references movies(id) on delete cascade,
  actor_id bigint references actors(id) on delete cascade,
  role text,
  primary key (movie_id, actor_id)
);

Foreign Key Actions

create table orders (
  id bigint primary key,
  customer_id bigint references customers(id)
    on delete cascade      -- Delete orders when customer is deleted
    on update cascade      -- Update orders when customer id changes
);

create table profiles (
  id bigint primary key,
  user_id bigint references users(id)
    on delete restrict     -- Prevent deletion if profile exists
);

Inserting Data

Single Row Insert

insert into movies (name, description, release_year, rating)
values (
  'The Matrix',
  'A computer hacker learns about the true nature of reality.',
  1999,
  8.7
);

Multiple Row Insert

insert into movies (name, release_year, rating)
values
  ('Inception', 2010, 8.8),
  ('Interstellar', 2014, 8.6),
  ('The Prestige', 2006, 8.5);

Insert with Returning

insert into users (name, email)
values ('John Doe', '[e-mail]')
returning id, created_at;

Using JavaScript Client

// Insert a single row
const { data, error } = await supabase
  .from('movies')
  .insert({
    name: 'The Dark Knight',
    release_year: 2008,
    rating: 9.0
  })
  .select()

// Insert multiple rows
const { data, error } = await supabase
  .from('movies')
  .insert([
    { name: 'Movie 1', release_year: 2020 },
    { name: 'Movie 2', release_year: 2021 }
  ])

Updating Data

-- Update specific rows
update movies
set rating = 9.0
where name = 'The Matrix';

-- Update multiple columns
update products
set
  price = price * 1.1,  -- Increase by 10%
  updated_at = now()
where category_id = 5;

-- Update with returning
update users
set last_login = now()
where id = 123
returning id, last_login;

Deleting Data

-- Delete specific rows
delete from movies
where release_year < 2000;

-- Delete with returning
delete from sessions
where expires_at < now()
returning id;

Views

Views provide reusable queries and can simplify complex data access.

Creating Views

create view movie_ratings as
  select
    m.name,
    m.release_year,
    m.rating,
    c.name as category
  from movies m
  left join categories c on m.category_id = c.id
  where m.rating >= 8.0;

-- Query the view
select * from movie_ratings;

Materialized Views

Materialized views cache query results for better performance:

create materialized view product_stats as
  select
    category_id,
    count(*) as total_products,
    avg(price) as average_price,
    sum(quantity) as total_inventory
  from products
  where deleted_at is null
  group by category_id;

-- Refresh the materialized view
refresh materialized view product_stats;

Table Modifications

Add Columns

alter table movies
add column director text;

alter table movies
add column budget numeric(12,2) default 0;

Modify Columns

-- Change data type
alter table movies
alter column rating type numeric(4,2);

-- Add NOT NULL constraint
alter table movies
alter column name set not null;

-- Drop NOT NULL constraint
alter table movies
alter column description drop not null;

Drop Columns

alter table movies
drop column budget;

Rename Tables and Columns

-- Rename table
alter table movies rename to films;

-- Rename column
alter table films rename column name to title;

Real Example from Supabase Source

Here’s a real table from the Supabase source code:

-- Error tracking table with soft deletes
create table content.error (
  code text not null,
  service uuid not null references content.service(id) on delete restrict,
  http_status_code smallint,
  message text,
  created_at timestamptz default now(),
  updated_at timestamptz default now(),
  deleted_at timestamptz default null,
  primary key (service, code)
);

-- Enable row level security
alter table content.error enable row level security;

-- Create index for non-deleted records
create index idx_content_error_service_code_nondeleted_only
  on content.error (service, code)
  where deleted_at is null;

-- Create policy for authenticated users
create policy content_error_authenticated_select_all
  on content.error
  for select
  to authenticated
  using (deleted_at is null);

Best Practices

Use meaningful table names

• Use lowercase with underscores: user_profiles, not UserProfiles
• Use plural nouns for tables: movies, users, orders
• Avoid generic names: data, info, temp

Always define primary keys

Every table should have a primary key for uniqueness and indexing.

Use appropriate data types

• Use timestamptz for timestamps (includes timezone)
• Use numeric for money (exact precision)
• Use text for variable-length strings
• Use jsonb for flexible structured data

Add timestamps

Include created_at and updated_at columns for audit trails.

Enable Row Level Security (RLS)

Protect your data with row-level security policies.

Next Steps

Functions

Create database functions for complex logic

Triggers

Automate actions with database triggers

Migrations

Manage schema changes with migrations

Indexes

Optimize query performance with indexes