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. Enabling row level security without a policy blocks every request, so a table in that state returns no rows to anyone:

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

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.

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."

Connect to your database directly and load the file with the COPY command. Name the columns the file contains, so Postgres doesn't expect a value for id:

1
psql -h DATABASE_URL -p 5432 -d postgres -U postgres \
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 -h DATABASE_URL -p 5432 -d postgres -U postgres \
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 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 bigint not null,
4
  actor_id bigint not null references public.actors
5
);

Reference#

Reference material for choosing a column type.

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