Home sql to handle potential for null result from subquery during insert statement

Questions

sql to handle potential for null result from subquery during insert statement

May 30, 2022

I am performing an insert, and using a subquery to select an email address for the insert statement, as such:

INSERT INTO users (id, email, date) VALUES (
123,
(
  SELECT email from list_of_emails WHERE email = 'abc@yahoo.com'
),
now() 
)

The users table has a non-null constraint on an email address, so what I am trying to catch is the potential that there may not be an email address that matches the subquery, in which case a not-null constraint violation occurs.

Any suggestion on how to catch this value error during the subquery and return some error message like "email address was not found"

>Solution :

Just use INSERT ... SELECT ...; form:

INSERT INTO users (id, email, date)
SELECT DISTINCT 123, email, now()
  FROM email
 WHERE email = 'abc@yahoo.com'
;

This avoids attempting to insert when no email rows are found. Hopefully, you don’t have duplicate email entries, as that would have been a problem in the original as well. I added a DISTINCT just in case, since I don’t know your schema. Remove the DISTINCT if email is guaranteed unique in the email table.