2

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

enter image description here

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 ...  
        ^

1 Answer 1

2

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:

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.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.