Tables and data

Learn what tables are and how to use them.

This guide is organized into several groups:

• What is a table? explains the basics if you're new to relational databases.
• Creating and managing tables is the action path: create a table, load rows into it, and link it to other tables.
• How tables are organized is the background: primary keys, relationships, and schemas.
• Reference lists the column data types that Postgres supports.

For saved queries that behave like tables, see Views.

What is a table?#

Tables are where you store your data.

Tables are similar to Excel spreadsheets. They contain columns and rows. For example, this table has 3 columns named id, name, and description, and 4 rows of data:

There are a few important differences from a spreadsheet, but it's a good starting point if you're new to relational databases.

Creating and managing tables#

Creating tables#

When creating a table, it's best practice to add columns at the same time.

You must define the data type of each column when you create it. You can add and remove columns at any time after creating a table.

Supabase provides several options for creating tables. You can use the Dashboard or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.

You now have a table with its columns defined. Before you put rows in it, protect it.

Securing your tables#

A table in the public schema is reachable through the Data API. Until you enable row level security and write a policy, anyone holding your project's publishable key can read and write every row in it.

The Table Editor enables row level security for you when you create a table in the Dashboard. When you create a table with SQL, enable it yourself.

Enabling row level security#

1. Enable row level security on the table:

   1
   alter table movies enable row level security;

2. Add a policy that describes who can read the table. Until one exists, Data API requests return no rows. The table's owner and roles with BYPASSRLS aren't subject to policies, which is why the same query still returns rows in the SQL editor:

   1
   create policy "Anyone can read movies"
   2
   on movies for select
   3
   to anon, authenticated
   4
   using ( true );

A policy decides which rows a role reaches, not whether it holds privileges on the table. The Data API roles carry the grants they need by default, so a request that fails with permission denied for table points at a revoked grant rather than a missing policy. See Securing your API.

For insert, update, and delete policies, and for how policies are evaluated, see Row Level Security.

Tables with different readers#

Most applications mix two kinds of table: shared data that everyone reads, and per-person data that only its owner reads. Each kind needs its own policy, and the shared one is the easiest to forget.

movies is the shared kind. The policy above lets anyone browse it, signed in or not.

The watchlists table is the other kind. Each row belongs to the person who created it, and only that person can read it:

1
create table watchlists (
2
  id bigint generated always as identity primary key,
3
  user_id uuid not null references auth.users default auth.uid(),
4
  movie_id bigint not null references movies
5
);
6
7
alter table watchlists enable row level security;
8
9
create policy "Users can read their own watchlist"
10
on watchlists for select
11
to authenticated
12
using ( (select auth.uid()) = user_id );
13
14
create policy "Users can add to their own watchlist"
15
on watchlists for insert
16
to authenticated
17
with check ( (select auth.uid()) = user_id );

Give both tables a policy, even when one of them is using ( true ). A shared table with row level security enabled and no policy is as unreachable as a private one.

Verifying your tables#

Confirm that every table exists and is protected before you build against it.

1. List the tables in the public schema and whether row level security is enabled on each one:

   1
   select tablename, rowsecurity
   2
   from pg_tables
   3
   where schemaname = 'public'
   4
   order by tablename;

2. Check that every table you meant to create appears in the results, and that rowsecurity is true for each one.

3. List the policies on those tables:

   1
   select tablename, policyname, cmd, roles
   2
   from pg_policies
   3
   where schemaname = 'public'
   4
   order by tablename, policyname;

4. Check that every table has at least one policy, and that any table meant to be readable by signed-out visitors lists anon among its roles.

Loading data#

There are several ways to load data in Supabase. You can load data directly into the database, or use the Data API. If you're loading large data sets, follow the bulk data loading instructions.

The read-only policy from Securing your tables rejects inserts through the Data API. Run the client examples below against a table that has an insert policy for the role you're using, or load the data over a direct connection instead.

Basic data loading#

1
insert into movies
2
  (name, description)
3
values
4
  (
5
    'The Empire Strikes Back',
6
    'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'
7
  ),
8
  (
9
    'Return of the Jedi',
10
    'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'
11
  );

Bulk data loading#

When inserting large data sets, use Postgres's COPY command. This loads data directly from a file into a table. COPY accepts text, CSV, and binary input.

For example, to load a CSV file into your movies table:

1
"The Empire Strikes Back","After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda."
2
"Return of the Jedi","After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star."

Set DATABASE_URL to your direct connection string, then load the file with the COPY command. Name the columns the file contains, so Postgres doesn't expect a value for id:

1
psql "$DATABASE_URL" \
2
  -c "\COPY movies (name, description) FROM './movies.csv' WITH (FORMAT csv);"

You can also pass options such as DELIMITER and HEADER, as defined in the Postgres COPY docs. HEADER skips the first line of the file, so use it only when that line names the columns:

1
psql "$DATABASE_URL" \
2
  -c "\COPY movies (name, description) FROM './movies-with-header.csv' WITH (FORMAT csv, HEADER, DELIMITER ';');"

If you receive an error FATAL: password authentication failed for user "postgres", reset your database password in Database Settings and try again.

Joining tables with foreign keys#

Foreign keys are how you express a relationship between two tables. For what that relationship means, see Relationships between tables.

In the movies example above, you might want to add a category for each movie, such as Action or Documentary. Create a new table called categories and link it to the movies table.

1
create table categories (
2
  id bigint generated always as identity primary key,
3
  name text -- category name
4
);
5
6
alter table movies
7
  add column category_id bigint references categories;

You can also create many-to-many relationships by creating a join table. For example, consider this situation:

• You have a list of movies.
• A movie can have several actors.
• An actor can perform in several movies.

1
create table actors (
2
  id bigint generated by default as identity primary key,
3
  name text
4
);
5
6
create table performances (
7
  id bigint generated by default as identity primary key,
8
  movie_id bigint not null references movies,
9
  actor_id bigint not null references actors
10
);

How tables are organized#

Background on the pieces the procedures above use. Read these when you want to know why a table is shaped the way it is.

Primary keys#

A table can have a primary key, a unique identifier for every row of data. A few tips for primary keys:

• Create a primary key for every table in your database.
• You can use any column as a primary key, as long as it is unique for every row.
• It's common to use a uuid type or a numbered identity column as your primary key.

1
create table movies (
2
  id bigint generated always as identity primary key
3
);

In the example above, you:

1. Created a column called id.
2. Assigned the data type bigint.
3. Instructed the database that this column is generated always as identity, so Postgres automatically assigns it a unique number.
4. Used it as the primary key, because the value is unique.

You can also use generated by default as identity, which lets you insert your own unique values.

1
create table movies (
2
  id bigint generated by default as identity primary key
3
);

Relationships between tables#

Tables can be joined together using foreign keys.

This is where the term relational comes from, because data typically forms some sort of relationship.

To create a foreign key, see Joining tables with foreign keys.

Schemas#

Tables belong to schemas. Schemas are a way of organizing your tables, often for security reasons.

If you don't explicitly pass a schema when creating a table, Postgres creates the table in the first schema in the current search_path. The default path is "$user", public, so on a new project that's the public schema.

You can create schemas to organize tables. For example, you might want a private schema that's hidden from your API:

1
create schema private;

Now you can create tables inside the private schema:

1
create table private.salaries (
2
  id bigint generated by default as identity primary key,
3
  salary numeric not null,
4
  actor_id bigint not null references public.actors
5
);

Reference#

Reference material for choosing a column type.

Choosing a type#

Postgres offers several near-equivalent types for the same job. These defaults are safe:

• Timestamps: prefer timestamptz over timestamp. timestamptz records the instant and renders it in the session's time zone. timestamp stores only the date and time fields, so the same stored value means different moments to clients in different zones. Reach for timestamp when you mean a wall-clock time rather than an instant, such as a 9 a.m. opening time that holds in every location.
• Text: prefer text over varchar(n). The two use the same storage representation, and text has no declared limit to migrate later. Add a check constraint when you need to bound the length.
• Money and other exact decimals: prefer numeric. real and double precision can't represent values such as 0.10 exactly, so totals drift as they accumulate. money is exact, but its fractional precision and formatting follow the server's lc_monetary setting, so the same value reads differently on another server.
• Identifiers: prefer bigint over integer. An integer tops out at 2,147,483,647, and an identity column doesn't reuse the values it skips, so a table reaches that ceiling before it holds that many rows.

Data types#

Every column has a data type. Postgres provides many default types, and you can design your own or use extensions if the default types don't fit your needs. You can use any data type that Postgres supports via the SQL editor. The Table Editor supports a subset of these, which keeps the experience focused for people with less database experience.

You can cast columns from one type to another, but some types are incompatible. For example, if you cast a timestamp to a date, you lose all the time information that was previously saved.

Resources#

• Official Docs: Create table
• Postgres Tutorial: Create tables
• Postgres Tutorial: Add column