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 ... ^
Best Answer
This would work:
Or you can still keep the
VALUES
expression to insert multiple rows at once: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 aWHERE
clause. Needs to be aSELECT
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: