supabase/apps/docs/content/guides/database/tables.mdx

---
id: 'tables'
title: 'Tables and Data'
description: 'Creating and using Postgres tables.'
video: 'https://www.youtube.com/v/TKwF3IGij5c'
---

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" (`id`, `name`, `description`) and 4 "rows" of data:

{/* supa-mdx-lint-disable Rule003Spelling */}

| `id` | `name`               | `description`                                                                                                                                                 |
| ---- | -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1    | The Phantom Menace   | Two Jedi escape a hostile blockade to find allies and come across a young boy who may bring balance to the Force.                                             |
| 2    | Attack of the Clones | Ten years after the invasion of Naboo, the Galactic Republic is facing a Separatist movement.                                                                 |
| 3    | Revenge of the Sith  | As Obi-Wan pursues a new threat, Anakin acts as a double agent between the Jedi Council and Palpatine and is lured into a sinister plan to rule the galaxy.   |
| 4    | Star Wars            | Luke Skywalker joins forces with a Jedi Knight, a cocky pilot, a Wookiee and two droids to save the galaxy from the Empire's world-destroying battle station. |

{/* supa-mdx-lint-enable Rule003Spelling */}

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

## Creating tables

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

<Image
  alt="Tables and columns"

src={{
    dark: '/docs/img/database/managing-tables/creating-tables.png',
    light: '/docs/img/database/managing-tables/creating-tables--light.png',
  }}
width={1600}
height={1145}
/>

You must define the "data type" of each column when it is created. 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](../../guides/database/connecting-to-postgres) to your database
and run the SQL queries yourself.

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="dashboard"
  queryGroup="database-method"
>
<TabPanel id="dashboard" label="Dashboard">

<video width="99%" muted playsInline controls={true}>
  <source
    src="https://xguihxuzqibwxjnimxev.supabase.co/storage/v1/object/public/videos/docs/api/api-create-table-sm.mp4"
    type="video/mp4"
  />
</video>

1. Go to the [Table Editor](/dashboard/project/_/editor) page in the Dashboard.
2. Click **New Table** and create a table with the name `todos`.
3. Click **Save**.
4. Click **New Column** and create a column with the name `task` and type `text`.
5. Click **Save**.

</TabPanel>
<TabPanel id="sql" label="SQL">

```sql
create table movies (
  id bigint generated by default as identity primary key,
  name text,
  description text
);
```

</TabPanel>
</Tabs>

<Admonition type="note">

When naming tables, use lowercase and underscores instead of spaces (e.g., `table_name`, not `Table Name`).

</Admonition>

## Columns

You must define the "data type" when you create a column.

### Data types

Every column is a predefined type. Postgres provides many [default types](https://www.postgresql.org/docs/current/datatype.html), and you can even 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. We only support a subset of these in the Table Editor in an effort to keep the experience simple for people with less experience with databases.

<details>
<summary>Show/Hide default data types</summary>

| `Name`                            | `Aliases`     | `Description`                                                    |
| --------------------------------- | ------------- | ---------------------------------------------------------------- |
| `bigint`                          | `int8`        | signed eight-byte integer                                        |
| `bigserial`                       | `serial8`     | autoincrementing eight-byte integer                              |
| `bit`                             |               | fixed-length bit string                                          |
| `bit varying`                     | `varbit`      | variable-length bit string                                       |
| `boolean`                         | `bool`        | logical Boolean (true/false)                                     |
| `box`                             |               | rectangular box on a plane                                       |
| `bytea`                           |               | binary data (“byte array”)                                       |
| `character`                       | `char`        | fixed-length character string                                    |
| `character varying`               | `varchar`     | variable-length character string                                 |
| `cidr`                            |               | IPv4 or IPv6 network address                                     |
| `circle`                          |               | circle on a plane                                                |
| `date`                            |               | calendar date (year, month, day)                                 |
| `double precision`                | `float8`      | double precision floating-point number (8 bytes)                 |
| `inet`                            |               | IPv4 or IPv6 host address                                        |
| `integer`                         | `int`, `int4` | signed four-byte integer                                         |
| `interval [ fields ]`             |               | time span                                                        |
| `json`                            |               | textual JSON data                                                |
| `jsonb`                           |               | binary JSON data, decomposed                                     |
| `line`                            |               | infinite line on a plane                                         |
| `lseg`                            |               | line segment on a plane                                          |
| `macaddr`                         |               | MAC (Media Access Control) address                               |
| `macaddr8`                        |               | MAC (Media Access Control) address (EUI-64 format)               |
| `money`                           |               | currency amount                                                  |
| `numeric`                         | `decimal`     | exact numeric of selectable precision                            |
| `path`                            |               | geometric path on a plane                                        |
| `pg_lsn`                          |               | Postgres Log Sequence Number                                     |
| `pg_snapshot`                     |               | user-level transaction ID snapshot                               |
| `point`                           |               | geometric point on a plane                                       |
| `polygon`                         |               | closed geometric path on a plane                                 |
| `real`                            | `float4`      | single precision floating-point number (4 bytes)                 |
| `smallint`                        | `int2`        | signed two-byte integer                                          |
| `smallserial`                     | `serial2`     | autoincrementing two-byte integer                                |
| `serial`                          | `serial4`     | autoincrementing four-byte integer                               |
| `text`                            |               | variable-length character string                                 |
| `time [ without time zone ]`      |               | time of day (no time zone)                                       |
| `time with time zone`             | `timetz`      | time of day, including time zone                                 |
| `timestamp [ without time zone ]` |               | date and time (no time zone)                                     |
| `timestamp with time zone`        | `timestamptz` | date and time, including time zone                               |
| `tsquery`                         |               | text search query                                                |
| `tsvector`                        |               | text search document                                             |
| `txid_snapshot`                   |               | user-level transaction ID snapshot (deprecated; see pg_snapshot) |
| `uuid`                            |               | universally unique identifier                                    |
| `xml`                             |               | XML data                                                         |

</details>

<br />

You can "cast" columns from one type to another, however there can be some incompatibilities between types.
For example, if you cast a `timestamp` to a `date`, you will lose all the time information that was previously saved.

### Primary keys

A table can have a "primary key" - a unique identifier for every row of data. A few tips for Primary Keys:

- It's recommended to 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.

```sql
create table movies (
  id bigint generated always as identity primary key
);
```

In the example above, we have:

1. created a column called `id`
1. assigned the data type `bigint`
1. instructed the database that this should be `generated always as identity`, which means that Postgres will automatically assign a unique number to this column.
1. Because it's unique, we can also use it as our `primary key`.

We could also use `generated by default as identity`, which would allow us to insert our own unique values.

```sql
create table movies (
  id bigint generated by default as identity primary key
);
```

## Loading data

There are several ways to load data in Supabase. You can load data directly into the database or using the [APIs](../../guides/database/api).
Use the "Bulk Loading" instructions if you are loading large data sets.

### Basic data loading

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="sql"
  queryGroup="language"
>
<TabPanel id="sql" label="SQL">

```sql
insert into movies
  (name, description)
values
  (
    '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.'
  ),
  (
    '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.'
  );
```

</TabPanel>
<TabPanel id="js" label="JavaScript">

```js
const { data, error } = await supabase.from('movies').insert([
  {
    name: 'The Empire Strikes Back',
    description:
      'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.',
  },
  {
    name: 'Return of the Jedi',
    description:
      'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.',
  },
])
```

</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">

```dart
await supabase
  .from('movies')
  .insert([{
    name: 'The Empire Strikes Back',
    description: 'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'
  }, {
    name: 'Return of the Jedi',
    description: 'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'
  }]);
```

</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">

```swift
try await supabase.from("movies")
  .insert(
    [
      [
        "name": "The Empire Strikes Back",
        "description":
          "After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.",
      ],
      [
        "name": "Return of the Jedi",
        "description":
          "After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.",
      ],
    ]
  )
  .execute()
```

</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">

```python
client.from_("movies").insert([
    {
        "name": "The Empire Strikes Back",
        "description": "After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda."
    },
    {
        "name": "Return of the Jedi",
        "description": "After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star."
    }
]).execute()
```

</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">

```kotlin
@Serializable
data class Movie(
    val name: String,
    val description: String
)
```

```kotlin
supabase
    .from("movies")
    .insert(listOf(
        Movie("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."),
        Movie("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."),
    ))
```

</TabPanel>
</$Show>
</Tabs>

### Bulk data loading

When inserting large data sets it's best to use Postgres's [COPY](https://www.postgresql.org/docs/current/sql-copy.html) command.
This loads data directly from a file into a table. There are several file formats available for copying data: text, CSV, binary, JSON, etc.

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

```text ./movies.csv
"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."
"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."
```

You would [connect](../../guides/database/connecting-to-postgres#direct-connections) to your database directly and load the file with the COPY command:

```bash
psql -h DATABASE_URL -p 5432 -d postgres -U postgres \
  -c "\COPY movies FROM './movies.csv';"
```

Additionally use the `DELIMITER`, `HEADER` and `FORMAT` options as defined in the Postgres [COPY](https://www.postgresql.org/docs/current/sql-copy.html) docs.

```bash
psql -h DATABASE_URL -p 5432 -d postgres -U postgres \
  -c "\COPY movies FROM './movies.csv' WITH DELIMITER ',' CSV HEADER"
```

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

## Joining tables with foreign keys

Tables can be "joined" together using Foreign Keys.

<Image
  alt="Foreign Keys"

src={{
    dark: '/docs/img/database/managing-tables/joining-tables.png',
    light: '/docs/img/database/managing-tables/joining-tables--light.png',
  }}
width={1600}
height={1145}
/>

This is where the "Relational" naming comes from, as data typically forms some sort of relationship.

In our "movies" example above, we might want to add a "category" for each movie (for example, "Action", or "Documentary").
Let's create a new table called `categories` and "link" our `movies` table.

```sql
create table categories (
  id bigint generated always as identity primary key,
  name text -- category name
);

alter table movies
  add column category_id bigint references categories;
```

You can also create "many-to-many" relationships by creating a "join" table.
For example if you had the following situations:

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

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="dashboard"
  queryGroup="database-method"
>
<TabPanel id="dashboard" label="Dashboard">

<div className="video-container">
  <iframe
    src="https://www.youtube-nocookie.com/embed/TKwF3IGij5c"
    frameBorder="1"
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
    allowFullScreen
  ></iframe>
</div>

</TabPanel>
<TabPanel id="sql" label="SQL">

```sql
create table movies (
  id bigint generated by default as identity primary key,
  name text,
  description text
);

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

create table performances (
  id bigint generated by default as identity primary key,
  movie_id bigint not null references movies,
  actor_id bigint not null references actors
);
```

</TabPanel>
</Tabs>

## Schemas

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

<Image
  alt="Schemas and tables"

src={{
    dark: '/docs/img/database/managing-tables/schemas.png',
    light: '/docs/img/database/managing-tables/schemas--light.png',
  }}
width={1600}
height={1145}
/>

If you don't explicitly pass a schema when creating a table, Postgres will assume that you want to create the table in the `public` schema.

We can create schemas for organizing tables. For example, we might want a private schema which is hidden from our API:

```sql
create schema private;
```

Now we can create tables inside the `private` schema:

```sql
create table private.salaries (
  id bigint generated by default as identity primary key,
  salary bigint not null,
  actor_id bigint not null references public.actors
);
```

## Views

A View is a convenient shortcut to a query. Creating a view does not involve new tables or data. When run, an underlying query is executed, returning its results to the user.

Say we have the following tables from a database of a university:

**`students`**

{/* supa-mdx-lint-disable Rule003Spelling */}

| id  | name             | type          |
| --- | ---------------- | ------------- |
| 1   | Princess Leia    | undergraduate |
| 2   | Yoda             | graduate      |
| 3   | Anakin Skywalker | graduate      |

{/* supa-mdx-lint-enable Rule003Spelling */}

**`courses`**

| id  | title                    | code    |
| --- | ------------------------ | ------- |
| 1   | Introduction to Postgres | PG101   |
| 2   | Authentication Theories  | AUTH205 |
| 3   | Fundamentals of Supabase | SUP412  |

**`grades`**

| id  | student_id | course_id | result |
| --- | ---------- | --------- | ------ |
| 1   | 1          | 1         | B+     |
| 2   | 1          | 3         | A+     |
| 3   | 2          | 2         | A      |
| 4   | 3          | 1         | A-     |
| 5   | 3          | 2         | A      |
| 6   | 3          | 3         | B-     |

Creating a view consisting of all the three tables will look like this:

```sql
create view transcripts as
    select
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    from grades
    left join students on grades.student_id = students.id
    left join courses on grades.course_id = courses.id;

grant all on table transcripts to authenticated;
```

Once done, we can now access the underlying query with:

```sql
select * from transcripts;
```

### View security

By default, views are accessed with their creator's permission ("security definer"). If a privileged role creates a view, others accessing it will use that role's elevated permissions. To enforce row level security policies, define the view with the "security invoker" modifier.

```sql
-- alter a security_definer view to be security_invoker
alter view <view name>
set (security_invoker = true);

-- create a view with the security_invoker modifier
create view <view name> with(security_invoker=true) as (
  select * from <some table>
);
```

### When to use views

Views provide several benefits:

- Simplicity
- Consistency
- Logical Organization
- Security

#### Simplicity

As a query becomes more complex, it can be a hassle to call it over and over - especially when we run it regularly. In the example above, instead of repeatedly running:

```sql
select
  students.name,
  students.type,
  courses.title,
  courses.code,
  grades.result
from
  grades
  left join students on grades.student_id = students.id
  left join courses on grades.course_id = courses.id;
```

We can run this instead:

```sql
select * from transcripts;
```

Additionally, a view behaves like a typical table. We can safely use it in table `JOIN`s or even create new views using existing views.

#### Consistency

Views ensure that the likelihood of mistakes decreases when repeatedly executing a query. In our example above, we may decide that we want to exclude the course _Introduction to Postgres_. The query would become:

```sql
select
  students.name,
  students.type,
  courses.title,
  courses.code,
  grades.result
from
  grades
  left join students on grades.student_id = students.id
  left join courses on grades.course_id = courses.id
where courses.code != 'PG101';
```

Without a view, we would need to go into every dependent query to add the new rule. This would increase in the likelihood of errors and inconsistencies, as well as introducing a lot of effort for a developer. With views, we can alter just the underlying query in the view **transcripts**. The change will be applied to all applications using this view.

#### Logical organization

With views, we can give our query a name. This is extremely useful for teams working with the same database. Instead of guessing what a query is supposed to do, a well-named view can explain it. For example, by looking at the name of the view **transcripts**, we can infer that the underlying query might involve the **students**, **courses**, and **grades** tables.

#### Security

Views can restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. We can prevent them from reading sensitive columns by excluding them from the underlying query.

### Materialized views

A [materialized view](https://www.postgresql.org/docs/12/rules-materializedviews.html) is a form of view but it also stores the results to disk. In subsequent reads of a materialized view, the time taken to return its results would be much faster than a conventional view. This is because the data is readily available for a materialized view while the conventional view executes the underlying query each time it is called.

Using our example above, a materialized view can be created like this:

```sql
create materialized view transcripts as
  select
    students.name,
    students.type,
    courses.title,
    courses.code,
    grades.result
  from
    grades
    left join students on grades.student_id = students.id
    left join courses on grades.course_id = courses.id;
```

Reading from the materialized view is the same as a conventional view:

```sql
select * from transcripts;
```

### Refreshing materialized views

Unfortunately, there is a trade-off - data in materialized views are not always up to date. We need to refresh it regularly to prevent the data from becoming too stale. To do so:

```sql
refresh materialized view transcripts;
```

It's up to you how regularly refresh your materialized views, and it's probably different for each view depending on its use-case.

### Materialized views vs conventional views

Materialized views are useful when execution times for queries or views are too slow. These could likely occur in views or queries involving multiple tables and billions of rows. When using such a view, however, there should be tolerance towards data being outdated. Some use-cases for materialized views are internal dashboards and analytics.

Creating a materialized view is not a solution to inefficient queries. You should always seek to optimize a slow running query even if you are implementing a materialized view.

## Resources

- [Official Docs: Create table](https://www.postgresql.org/docs/current/sql-createtable.html)
- [Official Docs: Create view](https://www.postgresql.org/docs/12/sql-createview.html)
- [Postgres Tutorial: Create tables](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/)
- [Postgres Tutorial: Add column](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-add-column/)
- [Postgres Tutorial: Views](https://www.postgresqltutorial.com/postgresql-views/)