Comment on page
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 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()
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.
As of version 15 Postgres supports composite primary and foreign key. Whalesync does not support composite keys. However we have received a number of request to support a foreign key of type array, which Postgres still does not support. Many users ask for this since many other connectors support arrays of foreign keys and our users want to sync with these.
We have created a mechanism for you to configure a Postgres column to be an array and signal to Whalesync to treat it as a foreign key array. Since from Postgres' perspective it is just an array, we loose the validation that any value placed in the array indeed is an id for a record in a foreign table. Despite this drawback, the functionality is stable and can be powerful. Note: a bad value here will not break Whalesync, that value will simply not sync until you correct it.
Here are the steps you need to take if you have tables Contacts and Company and want to add an array of foreign keys from one to the other:
- Make sure both tables have a proper primary key, we recommend a uuid for both.
- In table Company create a column "People" with type uuid[] to match the type of the table id. At the end of the column name add "_fk" as that will designate it as a foreign key to Whalesync. Then after that add "_Contacts" to designate that it should point to Contacts table (match capitalization). So the column will be then called "People_fk_Contacts".
- Set up your sync in Whalesync as normal. Map the foreign key array of one connector to the Postgres "People_fk_Contacts" in this connector. Save and sync.
CREATE TABLE public."Contacts" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Email" text );
CREATE TABLE public."Company" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Name" text,
"People_fk_Contacts" uuid[] );
Last modified 6mo ago