> Just create a unique index on EMAIL column and handle error if it comes
Thanks for your suggestion. Of course, I do understand that this could be enforced/imposed at the database-level at any time. But I'm trying to find out whether this could be solved at the application layer itself. Any thoughts/ideas?
Exactly at the application level you just need to ignore an unique constraint violation error reported by the backend. You may also wrap INSERT statement in the PL/pgSQL function or in the DO statement and catch the exception generated by the backend.