> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/supabase/supabase/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={null}
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:

```bash theme={null}
# Create a migration file
supabase migration new create_movies_table
```

```sql title="supabase/migrations/20241205075911_create_movies_table.sql" theme={null}
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

```sql theme={null}
-- 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

```sql theme={null}
create table employees (
  id bigint generated always as identity primary key,
  name text not null,
  email text unique
);
```

### UUID Primary Keys

```sql theme={null}
create table sessions (
  id uuid primary key default gen_random_uuid(),
  user_id bigint not null,
  created_at timestamptz default now()
);
```

### Composite Primary Keys

```sql theme={null}
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

```sql theme={null}
create table customers (
  id bigint primary key,
  email text not null,  -- Cannot be null
  name text not null
);
```

### UNIQUE Constraint

```sql theme={null}
create table users (
  id bigint primary key,
  username text unique,  -- Must be unique
  email text unique
);
```

### CHECK Constraint

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
insert into movies (name, release_year, rating)
values
  ('Inception', 2010, 8.8),
  ('Interstellar', 2014, 8.6),
  ('The Prestige', 2006, 8.5);
```

### Insert with Returning

```sql theme={null}
insert into users (name, email)
values ('John Doe', 'john@example.com')
returning id, created_at;
```

### Using JavaScript Client

```javascript theme={null}
// 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
alter table movies
add column director text;

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

### Modify Columns

```sql theme={null}
-- 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

```sql theme={null}
alter table movies
drop column budget;
```

### Rename Tables and Columns

```sql theme={null}
-- 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:

```sql theme={null}
-- 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

<AccordionGroup>
  <Accordion title="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`
  </Accordion>

  <Accordion title="Always define primary keys">
    Every table should have a primary key for uniqueness and indexing.
  </Accordion>

  <Accordion title="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
  </Accordion>

  <Accordion title="Add timestamps">
    Include `created_at` and `updated_at` columns for audit trails.
  </Accordion>

  <Accordion title="Enable Row Level Security (RLS)">
    Protect your data with row-level security policies.
  </Accordion>
</AccordionGroup>

## Next Steps

<CardGroup cols={2}>
  <Card title="Functions" icon="function" href="/database/functions">
    Create database functions for complex logic
  </Card>

  <Card title="Triggers" icon="bolt" href="/database/triggers">
    Automate actions with database triggers
  </Card>

  <Card title="Migrations" icon="code-branch" href="/database/migrations">
    Manage schema changes with migrations
  </Card>

  <Card title="Indexes" icon="gauge-high" href="/database/overview">
    Optimize query performance with indexes
  </Card>
</CardGroup>