Fix Supabase CHARINDEX does not exist (SQLSTATE 42883)

TL;DR#

Replace the CHARINDEX call in the profile-copy trigger with PostgreSQL's strpos, putting the email string first and the @ delimiter second. Changing only the function name leaves the arguments backwards. Keep the username expression as one value for one target column, then verify that the user insert creates the expected profile.

The reported Supabase incident copied a new user's email into profiles and derived user_name from its local part. For test.123@test.com, the requested username was test-123. Its failing expression used CHARINDEX inside an insert trigger:

left(replace(new.email, '.', '-'),
     charindex('@', replace(new.email, '.', '-')) - 1)

The question reports function charindex(unknown, text) does not exist with SQLSTATE 42883. The same failure was reproduced on PostgreSQL 18.6, using the official PostgreSQL Docker image, on 13 October 2026. The local fixture copied between two isolated tables; it did not exercise a hosted Supabase signup. Its raw error was:

psql:/work/repro.sql:21: ERROR:  42883: function charindex(unknown, text) does not exist

Why it happens#

CHARINDEX is documented by Microsoft for Transact-SQL. The database executing this trigger is PostgreSQL. A function name from another SQL dialect does not become available just because its arguments are text.

The PostgreSQL alternative is strpos in the string-functions reference. It searches its first argument for its second argument. The trigger can use that built-in normally: NEW supplies the inserted row in a row-level trigger, as the trigger-function reference explains.

This is a function-call error, not evidence that the Auth request needs more privileges. Adding a text cast cannot introduce an absent built-in. The text-valued replacement expression already resolves to text in the local reproduction.

Fix#

1. Read the function attached to the failing insert#

Find the actual trigger before editing a similarly named function. Supabase's user-management documentation places Auth users in auth.users: auth is the schema, rather than the name of a standalone user table.

select t.tgname,
       pg_get_functiondef(t.tgfoid) as function_definition
from pg_trigger as t
where t.tgrelid = 'auth.users'::regclass
  and not t.tgisinternal;

The PostgreSQL information-functions reference documents pg_get_functiondef. Read the returned body and locate the username calculation. If there are several triggers, use the function name in the error's context to identify which one failed.

Also confirm that profiles has the expected columns and types. The PostgreSQL table-inspection guide shows how to inspect a table from psql or an ordinary SQL client. Fixing the expression does not add missing columns, rename user_name or reconcile a different profile schema.

2. Replace the call and its argument order#

For the source's expression, the corrected scalar calculation is:

left(replace(new.email, '.', '-'),
     strpos(replace(new.email, '.', '-'), '@') - 1)

The replacement preserves the existing dot-to-hyphen transformation. It changes the unsupported lookup to the supported function, and supplies that function's arguments in its documented order. It does not require a new extension, a new trigger event or a fresh username column.

For a trigger whose existing function is public.handle_new_user and whose profiles table has id, email and user_name, the complete function can be:

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $body$
begin
  insert into public.profiles (id, email, user_name)
  values (
    new.id,
    new.email,
    left(replace(new.email, '.', '-'),
         strpos(replace(new.email, '.', '-'), '@') - 1)
  );
  return new;
end;
$body$;

Use the name discovered in the previous step. Creating a different function will not redirect an existing trigger to it. The security-definer and empty-search-path layout follows Supabase's documented profile-trigger template; schema-qualified table access is intentional. Preserve the real function's other required behaviour and review its ownership instead of replacing unrelated business logic wholesale.

Apply the change through your normal migration process. If an accompanying table alteration fails with must be owner of table, that is a separate ownership problem covered by the Supabase db push ownership fix. It is not solved by renaming the string function again.

3. Check the built-in independently of Auth#

The original reporter also said a strpos attempt failed, but the question does not include that attempted call. Do not infer that PostgreSQL lacks the function. Check the exact signature and a scalar call:

select to_regprocedure('pg_catalog.strpos(text,text)') as builtin_signature,
       pg_catalog.strpos('test.123@test.com', '@') as correct_position,
       pg_catalog.strpos('@', 'test.123@test.com') as reversed_position;

The reproduction found the built-in signature and returned positions nine and zero respectively. Reversing two text arguments runs, but searches for the entire email inside the single-character delimiter. That is the wrong calculation, even though it produces no undefined-function error.

A separate negative control called strpos with only one argument and produced function strpos(unknown) does not exist. That establishes one way a malformed call can raise the same error class; it does not establish what the source's author actually tried. If the real error persists, compare its argument count and types with the documented two-text-argument signature.

Verify the fix#

In the isolated Docker fixture, the failing trigger left both source and profile tables empty. Changing only its CHARINDEX call expression to the correctly ordered strpos expression made the same insertion succeed and stored test-123 as user_name. No permission, table definition or trigger event changed between those two attempts.

That rollback is expected: the PostgreSQL trigger overview states that the trigger and its initiating statement run in the same transaction. An AFTER trigger can still abort the insertion; AFTER does not mean it runs outside the transaction.

For the hosted application, create a fresh test user through its supported signup path, then inspect the resulting profile:

select id, email, user_name
from public.profiles
where email = 'test.123@test.com';

Confirm the matching identity and derived username, then check the database logs for the original undefined-function error. Avoid inserting synthetic rows directly into a production auth.users table to test this expression. If signup still reports a generic Database error saving new user, inspect the new underlying error before changing more SQL.

The answer's extra expression is not another profile column#

The source answer shows the username calculation followed by another comma-separated strpos call. That is useful for inspecting a position alongside a calculated string, but it supplies two expressions. Copying both into the third value of the original three-column insert supplies four values overall.

The local control retained the original three targets and added that extra position expression. PostgreSQL rejected it with INSERT has more expressions than target columns. Keep only the username expression in the write. Inspect positions in a separate SELECT rather than modifying the destination schema to accommodate debugging output.

Missing delimiters and colliding local parts#

The extraction is not email validation. The local controls showed that a string without @ gives a zero position; subtracting one and passing it to left removes the final character. For missing-at, that expression produced missing-a. A null input produced null in the tested expression.

For ordinary email inputs, an alternative avoids the position arithmetic:

select replace(split_part('test.123@test.com', '@', 1), '.', '-') as user_name;

The control returned test-123. A missing delimiter instead left the whole input intact, which is a different behaviour, not proof of a valid address. Choose an explicit rule for absent email data and check it against the constraints of your actual profile table.

Finally, the local part is not necessarily unique. In a separate schema variant, making user_name unique caused an email with the same local part on another domain to fail with a duplicate-key error. That is a new constraint failure after the string-function fix, not CHARINDEX returning. The PostgreSQL duplicate-key guide explains the separate conflict problem; decide how usernames are allocated before adding a uniqueness rule. Keep the repaired function call, and diagnose the exact next error rather than suppressing every exception from the profile trigger.

Further reading: Fix: TypeORM "array is not supported in postgres".