Handy snippets to help you set up Postgres to work with Whalesync

Primary Key – Create

The best way to create the primary key is to define it as part of creating a table. In the example below, there are two options: one using UUIDs and another using integers as primary keys (the latter is commented out). In both cases the primary key is set to auto-generate a key when a record is inserted into the table.
CREATE TABLE my_table (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
--id serial PRIMARY KEY,
description text,
is_published boolean );
*Note that Postgres allows only one primary key.

Primary Keys – Alter

If the table already exists and you want to change the primary key, you'll need to remove the old one and add a new one.
ALTER TABLE public."Table" DROP COLUMN "id";
ALTER TABLE public."Table" ADD PRIMARY KEY ("uuid");
*Supabase blocks access to this, so if you're using Supabase you will need to use their UI.

Foreign Keys – Add

Whalesync supports foreign keys. After creating your tables, you can designate a foreign key and then point it to another table's primary key.
-- AddForeignKey
ALTER TABLE entities
ADD CONSTRAINT entities_category_fkey
FOREIGN KEY (category)
REFERENCES categories(name);

Primary Key – Generate

Whalesync requires that the primary key is generated (i.e. has an automatic default value). These are a few examples:
We also support serial integers as the primary column (these are self-incrementing integers and equivalent to using the nextval function).