How to check other table for value during insert

Question

In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?

I have attempted to use WHERE NOT EXISTS as I want to not go on with the INSERT if I don't find the data I'm looking for in another table but I get the following error:

Database Model

Query

INSERT INTO car_model(model_name, car_make, date_released)
            VALUES("Classic Car", "Car Brand", "2000-01-01")
WHERE NOT EXISTS (
   SELECT * FROM car_make
   WHERE company_name = car_make AND date_released >= date_incorperated)

Error:

ERROR:  syntax error at or near "WHERE"  
LINE 3: WHERE NOT EXISTS (SELECT * FROM car_make WHERE company_name ...  
        ^

Erwin Brandstetter · Accepted Answer · 2020-04-22T03:59:43.713

This would work:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (SELECT 'Classic Car', 'Car Brand', '2000-01-01'::date)
  AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Or you can still keep the VALUES expression to insert multiple rows at once:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (
   VALUES 
   ('Classic Car', 'Car Brand', '2000-01-01'::date)
    -- more rows?
   ) AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Note how it may now be necessary to add explicit type casts. See:

Casting NULL type when updating multiple rows

You had a couple of issues:

A VALUES expression does not take a WHERE clause. Needs to be a SELECT for the purpose.
Values are enclosed in single quotes (''). Double quotes ("") are for identifiers.
To refer to input values, you need to form a derived table and attach column aliases first - with a subquery (like I did) or a CTE.

How to check other table for value during insert

Database Model

Query

1 Answers1