Links

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.

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.
ALTER TABLE public."Table"
ADD CONSTRAINT table_category_fkey
FOREIGN KEY ("category")
REFERENCES categories("name");
In the example above we are adding to a table called "Table" to an existing column called "category" a foreign key pointing to column called "name" in a table called "categories". The example creates the foreign key relationship, it does not create any tables and columns, they must already all exist.

Primary Key – Generate

Whalesync requires that the primary key is generated (i.e. has an automatic default value). These are a few examples:
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, that won't continuously update. If you have designed such column as your primary key we will accept it.
now()