Tables

Tables are similar to excel spreadsheets. They contain columns & rows of data. There are a few key differences from a spreadsheet however:

  • Every column is a strict type of data. When you set up a column, you must define what "data type" it is.
  • Tables can be joined together through relationships. For example you can have a "users" table, which is joined to a "teams" table.
create table table_name (
id integer primary key,
data jsonb,
name text
);
# with schema
create table schema_name.table_name (
id integer primary key,
data jsonb,
name text
);

Notes

  • Tables contain columns, rows, triggers, comments,
  • It is best practice to use lowercase and underscores when naming tables. For example: table_name, not Table Name.
  • Tables belong to schemas. If you don't explicitly pass the schema, Postgres will assume that you want to create the table in the public schema.

Examples

Create table

create table table_name (
id integer primary key,
data jsonb,
name text
);
# with schema
create table schema_name.table_name (
id integer primary key,
data jsonb,
name text
);

Primary keys using multiple columns

create table table_name (
column_1 data_type,
column_2 data_type
-- Constraints:
primary key (column_1, column_2)
);

Multiple foreign keys to the same table

alter table table_name
add constraint constraint_name_1 foreign key (colum_1) references foreign_table(id),
add constraint constraint_name_2 foreign key (colum_2) references foreign_table(id);

Delete a table

delete table if exists table_name;