> For the complete documentation index, see [llms.txt](https://docs.whalesync.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.whalesync.com/connectors/postgres/how-to-sync-postgres-views.md). # How to sync Postgres views Whalesync can't modify Postgres views safely, so it won't create its usual primary key column on a view the way it does for regular tables. Views still need a unique column for tracking each record — you add it yourself, and this page shows how. ## What Whalesync looks for A column on the **view itself** named exactly `whalesync_postgres_id` (all lowercase). That's the only thing Whalesync checks for — it doesn't matter whether the column comes from the underlying table, an alias, or an expression. Once the column exists, click **Refresh tables** in Whalesync and the view is ready to sync. ## What the column's values must satisfy Whalesync uses this column as the ID of each record, so its values must be: 1. **Unique** — no two rows of the view ever share a value. 2. **Non-null** — every row has a value. 3. **Stable** — a row keeps the same value for its whole life. The value must never change when the row is updated. Any column type works (`uuid`, `integer`, `bigint`, `text`, …) as long as the values meet those three rules. Whalesync does not validate them up front: duplicates, nulls, or changing values will cause records to be skipped, merged together, or treated as deleted and re-created during sync. ## Option 1: expose an existing column (no changes to the underlying table) If the underlying table already has a compatible column — typically its primary key — you don't have to modify the table at all. Append it to the view's select list under the required name: ```sql CREATE OR REPLACE VIEW public.my_view AS SELECT t.name, t.created_at, t.id AS whalesync_postgres_id -- existing unique, non-null, stable column FROM public.my_table t; ``` Note: `CREATE OR REPLACE VIEW` can only **append new columns at the end** of the view's column list — it can't rename or reorder existing output columns. Adding `t.id AS whalesync_postgres_id` as a new last column works even if the view already exposes `t.id` under another name. If you need to restructure the view instead, `DROP VIEW` and recreate it (watch out for dependent objects). ### Views over joins or aggregates If the view joins tables or groups rows so that no single base-table column is unique per view row, build a stable composite value from the keys that define each row: ```sql CREATE OR REPLACE VIEW public.my_view AS SELECT o.placed_at, c.name, (o.id::text || ':' || c.id::text) AS whalesync_postgres_id FROM public.orders o JOIN public.customers c ON c.id = o.customer_id; ``` This is unique and stable as long as the combined keys are. ## Option 2: add a new column to the underlying table If no existing column qualifies, add the same column Whalesync would create on a regular table, then include it in the view: ```sql ALTER TABLE public.my_table ADD COLUMN whalesync_postgres_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE; CREATE OR REPLACE VIEW public.my_view AS SELECT t.name, t.created_at, t.whalesync_postgres_id FROM public.my_table t; ``` ## Let an AI assistant write the query for you Copy the prompt below into Claude Code (or another AI assistant), paste in the SQL for your view and the tables it reads from (see [Getting the SQL to paste](#getting-the-sql-to-paste)), then run the SQL it returns in the Supabase SQL editor or your Postgres client. ``` Read https://docs.whalesync.com/connectors/postgres/how-to-sync-postgres-views.md for context. My Postgres view needs a column named exactly `whalesync_postgres_id`. Its values must be unique across all rows of the view, never null, and never change for the life of a row — Whalesync reads it as each record's ID. Prefer exposing an existing compatible column (such as the underlying table's primary key) by appending it to the view's select list as `AS whalesync_postgres_id`. Remember that `CREATE OR REPLACE VIEW` can only append new columns at the end of the column list. If the view joins or aggregates tables so that no single column is unique per row, build a stable composite value from the keys that define each row. Only if nothing compatible exists, add a column `whalesync_postgres_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE` to the underlying table and include it in the view. Tell me which existing column you chose and why it satisfies the uniqueness, non-null, and stability requirements with minimal jargon and simple language. Then give me the SQL to run in a format that's easy to copy. Here is the SQL for my view and the tables it reads from: -- paste the SQL for your view and its underlying tables here ``` ### Getting the SQL to paste The assistant needs two things: the definition of the **view** and the definitions of the **tables it reads from** — including their names, so it can write SQL you can run as-is. The easiest way is to copy the full SQL from your database tool: * **Supabase**: go to Database → Tables, open the "…" menu on the view and on each table it reads from, and choose **Copy table schema**. Paste everything into the prompt. * **Other tools** (TablePlus, DBeaver, DataGrip, pgAdmin, …): open the view and each underlying table and copy the SQL from the **Schema** or **DDL** tab. If your tool doesn't offer that, run this query instead after replacing every `public.my_view` with the name of your view. ```sql SELECT 'my_view' AS view_name, pg_get_viewdef('my_view', true) AS view_definition; ``` Paste both into the prompt, along with a description of each underlying table — at minimum its name, its columns, and which column is the primary key. ## After adding the column Go back to Whalesync and click **Refresh tables**. The view will drop out of the "needs setup" state and can be mapped and synced like any other table. ## Video Tutorial {% hint style="warning" %} This video tutorial is a little out of date and may not match the current UI. It also only covers the approach of adding a column to the underlying table. {% endhint %} {% embed url="" %}