Snippets
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.
CREATE TABLE public."Table" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
--id serial PRIMARY KEY,
"description" text,
"is_published" boolean );
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 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.
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()
Last modified 2mo ago