Skip to main content

Tables and Data

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:

idnamedescription
1The Phantom MenaceTwo Jedi escape a hostile blockade to find allies and come across a young boy who may bring balance to the Force.
2Attack of the ClonesTen years after the invasion of Naboo, the Galactic Republic is facing a Separatist movement.
3Revenge of the SithAs 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.
4Star WarsLuke 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.

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.

Tables and columns

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 to your database and run the SQL queries yourself.

1. Go to the "Table editor" section.
2. Click "New Table".
3. Enter the table name "todos".
4. Click "Save".
5. Click "New Column".
6. Enter the column name "task" and make the type "text".
7. Click "Save".

It is best practice to use lowercase and underscores when naming tables. For example: table_name, not Table Name.

Columns​

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

Data types​

Every column is a predefined type. PostgreSQL provides many default types, and you can even design your own (or use extensions) if the default types don't fit your needs.

Show/Hide default data types
NameAliasesDescription
bigintint8signed eight-byte integer
bigserialserial8autoincrementing eight-byte integer
bitfixed-length bit string
bit varyingvarbitvariable-length bit string
booleanboollogical Boolean (true/false)
boxrectangular box on a plane
byteabinary data (β€œbyte array”)
charactercharfixed-length character string
character varyingvarcharvariable-length character string
cidrIPv4 or IPv6 network address
circlecircle on a plane
datecalendar date (year, month, day)
double precisionfloat8double precision floating-point number (8 bytes)
inetIPv4 or IPv6 host address
integerint, int4signed four-byte integer
interval [ fields ]time span
jsontextual JSON data
jsonbbinary JSON data, decomposed
lineinfinite line on a plane
lsegline segment on a plane
macaddrMAC (Media Access Control) address
macaddr8MAC (Media Access Control) address (EUI-64 format)
moneycurrency amount
numericdecimalexact numeric of selectable precision
pathgeometric path on a plane
pg_lsnPostgreSQL Log Sequence Number
pg_snapshotuser-level transaction ID snapshot
pointgeometric point on a plane
polygonclosed geometric path on a plane
realfloat4single precision floating-point number (4 bytes)
smallintint2signed two-byte integer
smallserialserial2autoincrementing two-byte integer
serialserial4autoincrementing four-byte integer
textvariable-length character string
time [ without time zone ]time of day (no time zone)
time with time zonetimetztime of day, including time zone
timestamp [ without time zone ]date and time (no time zone)
timestamp with time zonetimestamptzdate and time, including time zone
tsquerytext search query
tsvectortext search document
txid_snapshotuser-level transaction ID snapshot (deprecated; see pg_snapshot)
uuiduniversally unique identifier
xmlXML data

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.
create table movies (
id bigint generated always as identity primary key
);

In the example above, we have:

  1. created a column called id
  2. assigned the data type bigint
  3. instructed the database that this should be generated always as identity, which means that Postgres will automatically assign a unique number to this column.
  4. Becuase 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.

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. Use the "Bulk Loading" instructions if you are loading large data sets.

Basic data loading​

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.');

Bulk data loading​

When inserting large data sets it's best to use PostgreSQL's COPY 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:

./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 to your database directly and load the file with the COPY command:

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

Joining tables with Foreign Keys​

Tables can be "joined" together using Foreign Keys.

Foreign Keys

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.

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 perfom in several movies.
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
);

Schemas​

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

Schemas and tables

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:

create schema private;

Now we can create tables inside the private schema:

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

Resources​