Handy snippets to help you set up Postgres to work with Whalesync
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.
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.
-- Delete the old primary key.
ALTER TABLE public."Table"
DROP COLUMN "id";
-- Add a new generated primary key.
ALTER TABLE public."Table"
ADD COLUMN "uuid" uuid PRIMARY KEY DEFAULT gen_random_uuid();
If the table already exists and you already have a generated key and want to designate it as a primary key.
ALTER TABLE public."Table"
ADD CONSTRAINT table_pk PRIMARY KEY ("uuid");
*Supabase blocks access to altering a table outside of its UI, thus please use Supabase's UI.
Whalesync requires that the primary key is generated (i.e. has an automatic default value). These are a few examples of functions that would generate data for you:
uuid_genetate_v4()
gen_random_uuid()
nextval('column_name')
We also support serial integers as the primary column (these are self-incrementing integers and equivalent to using the nextval function).