Answer to question asked
I though that the trigger would fire first, then the unique constraint ...
You thought right. But the main error was
.organization_id instead of
NEW.organization_id in the
UPDATE - which is bound to do nothing in the
INSERT case, where
OLD is not defined.
This used to raise and exception immediately in older Postgres versions. The release notes of Postgres 11:
In PL/pgSQL trigger functions, the
NEW variables now read as
NULL when not assigned (Tom Lane)
Previously, references to these variables could be parsed but not
Goes to show the importance of declaring the Postgres version in use ...
CREATE OR REPLACE FUNCTION public.ensure_only_one_default_address()
LANGUAGE plpgsql AS
-- nothing to do if updating the row currently enabled
IF TG_OP = 'UPDATE' THEN
IF OLD.is_default THEN -- !!!
SET is_default = false
AND organization_id = NEW.organization_id; -- !!! NEW, not OLD
CREATE TRIGGER ensure_only_one_default_address
BEFORE INSERT OR UPDATE OF is_default ON public.company_addresses
FOR EACH ROW WHEN (NEW.is_default)
EXECUTE PROCEDURE public.ensure_only_one_default_address();
I did more:
It's not safe to reference
OLD in the
INSERT case on the outer level. (Postgres is free to evaluate expressions in arbitrary sequence.) I hid that in a nested
Typically, it's cleaner to just write separate trigger functions and triggers for
UPDATE (unless this leads to massive code duplication).
For dynamic SQL, pass the value
OLD.organization_id as value.
But why dynamic SQL in the first place? I made it static. Cleaner, faster.
is_default = true is just a noisy way of saying
Alternative with CTE and no trigger
WITH pre_emptive AS (
SET is_default = false
AND organization_id = 2
INSERT INTO company_addresses(organization_id, is_default) VALUES (2, true);
That said, I suggest a completely different approach:
Alternative db design
It's inefficient to update other rows to mark a new one as "default".
Consider to add am email column for each organization that holds (and points to) the "default" instead:
CREATE TABLE company_addresses (
organization_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, organization text UNIQUE NOT NULL
, email text UNIQUE -- FK added below -- can also be NOT NULL
CREATE TABLE email (
email text PRIMARY KEY
, organization_id int NOT NULL REFERENCES company_addresses ON DELETE CASCADE
, UNIQUE (organization_id, email) -- seems redundant, but required for FK
ALTER TABLE company_addresses
ADD CONSTRAINT company_addressesn_default_email_fkey
FOREIGN KEY (organization_id, email) REFERENCES email (organization_id, email);
Then you don't need a trigger at all. And there are various other advantages - like you have the default email in the main table without join.