Primary Key Snippets

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 public."Table" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
--id serial PRIMARY KEY,
"description" text,
"is_published" boolean );

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.

-- 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.

Primary Key – Generate

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

Additionally many Postgres tables have a column with an autogenerated timestamp at the time a record is created. Make sure that this is a stable value (record created), that won't continuously update (record updated). If you have designed such column as your primary key we will accept it.

now()