'Scan error on column confirmation_token: converting NULL to string is unsupported' during Auth login

If you encounter a HTTP 500 error during authentication with the message error finding user: sql: Scan error on column "confirmation_token": converting NULL to string is unsupported, this typically indicates that the GoTrue Auth service found a NULL value in the auth.users.confirmation_token column, where a non-nullable string is expected.

To resolve this, update the confirmation_token to an empty string where it is NULL. You can execute the following SQL query in the SQL Editor:

1
update auth.users set confirmation_token = '' where confirmation_token is null;