PostgreSQL - Any way I can define a column that will cast empty string to null on insert or update?-CodePudding

I am using PostgreSQL 12. I have 'users' table where 'email' column is nullable means optional column, however it has unique constraint as expected. This 'email' column is 'citext' type column.

The problem is even I send 'email' value as NULL, the database saves it as empty string instead NULL in 'email' column during insert. Is there any way I can cast empty string to NULL for 'email' column on insert or update? Because it has unique constraint and it needs to be NULL if multiple users did provide 'email' to avoid unique constraint error.

I am using PHP Laravel framework. There is no mutator defined. The controller part is following:

    var_dump($user->email); // email is null still

    $success = $user->save(); // save DB changes

    var_dump($user->email); // email is '' string now

Please note that, I expect the casting should happens in background or by PostgreSQL itself automatically during insert or update. May be something like 'CHECK' constraint there that will filter automatically?

Thanks in advance.

CodePudding user response：

You could use NULLIF()

INSERT INTO your_table (cols..., email) VALUES (..., NULLIF(your_email, ''))

CodePudding user response：

No, as @a_horse_with_no_name noted, a check constraint will not help. Here is a trigger definition that should fix your case.

create or replace function emailfix_tf()
returns trigger language plpgsql as
$$
begin
  new.email := nullif(trim(new.email), '');
  return new;
end;
$$;

create trigger users_emailfix_t
before insert or update on users
for each row execute procedure emailfix_tf();

Anyway it's a pity that you have to define a trigger in order to fix data that has been malformed and spoiled by your ORM.