postgresql - DB/SQL best practice: Validate data before insert, or TRY to insert and use error message -

this general software engineering principle question. "better":

• to validate data correct before inserting database, or
• to try insert, , interpret error message if doesn't succeed.

ok. example: want add new movie database. movie must have unique name. try insert it, , let unique constraint catch it. -or- first select-query check if such movie exists?

a movie belongs genere. do select query first see if genre exists in database, or let foreign key constraint "catch"/validate it?

the problem checking beforehand ofc race condition -- other thread can insert movie title while you're validating -- there's no guarantee in multithreaded system. on other hand, error message (postgre)sql in plain english, it's not structured data (like e.g. json), requires parse in order understand went wrong generate non-technincal error message end user.

is there best pracitice on this? has got common dilemma?

the best practice have database work check uniqueness.

there 2 main reasons why. first, if need name unique, want database maintain constraint on data. so, database going check anyway. doing additional check duplicates effort, decreases performance.

second, in multi-threaded environment, can have race conditions. there lag between check make in code , insert statement. thread inserting same value @ same time. can around lag using transactions, decreases performance.