Log inStart free

NEW is null in a trigger function

Why NEW (or a field of it) is null inside a Postgres trigger. Covers statement-level triggers, DELETE triggers, wrong return values and missing user metadata in sign-up triggers.

What you see

One of these inside a trigger function:

ERROR:  55000: record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

ERROR:  23502: null value in column "message_id" of relation "audit_log" violates not-null constraint

Or no error at all: rows are silently not inserted, or a column the trigger was meant to fill stays empty.

Why it happens

NEW holds the row being written, but only in some kinds of trigger:

Trigger NEW OLD
for each row, INSERT the new row null
for each row, UPDATE the row after the change the row before
for each row, DELETE null the deleted row
for each statement null null

So the usual causes are:

  • The trigger is for each statement. It fires once per statement with no row. for each statement is the default if you leave it out.
  • The trigger also fires on DELETE and the function reads NEW unconditionally.
  • A before trigger returns the wrong thing. Returning null from a row-level before trigger cancels the write for that row with no error. On DELETE, return OLD.
  • The row is fine but the field is empty. A sign-up trigger reads NEW.raw_user_meta_data ->> 'display_name', but the app never sent that key.

How to fix it

  1. Check how the trigger is defined:

    select tgname, pg_get_triggerdef(oid)
from pg_trigger
where tgrelid = 'public.messages'::regclass and not tgisinternal;

    If it says FOR EACH STATEMENT, recreate it per row:

    drop trigger if exists messages_audit on public.messages;

create trigger messages_audit
after insert or update or delete on public.messages
for each row execute function public.audit_message();

  2. Branch on TG_OP and use the right record:

    create or replace function public.audit_message()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if tg_op = 'DELETE' then
    insert into public.audit_log (message_id, action) values (old.id, tg_op);
    return old;
  end if;

  insert into public.audit_log (message_id, action) values (new.id, tg_op);
  return new;
end;
$$;

    In an after trigger the return value is ignored, but returning the record keeps the function safe to reuse as a before trigger.

  3. For sign-up triggers on auth.users, send the metadata when signing up and give it a fallback:

    await supabase.auth.signUp({
  email,
  password,
  options: { data: { display_name: 'Sam Rivera' } },
})

    insert into public.profiles (id, display_name)
values (
  new.id,
  coalesce(new.raw_user_meta_data ->> 'display_name', split_part(new.email, '@', 1))
);

    OAuth providers fill metadata with their own keys (often full_name, name or avatar_url), so look at a real user's raw_user_meta_data in Studio before relying on a key.

  4. Debug with raise log or raise notice:

    raise notice 'op=%, new=%, old=%', tg_op, new, old;

    Notices show in Studio's SQL editor when you fire the trigger there.

On SupaHost

Studio's Logs pages aren't available, so raise log output from triggers fired by your app isn't searchable in the dashboard. Reproduce the write in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio) and use raise notice to see the values.

Updated 11 November 2026 · View as Markdown · Something wrong or missing? Tell us