Inserting unique value into database PGSQL/MSSQL

Question

I'm trying to insert unique records in a MSSQL and Postgresql DB using insert into where not exists. But I am getting a incorrect syntax error as seen below. What am I doing wrong?

INSERT INTO settings (id, title, description)
VALUES  (1, 'imageHeight', 'Image Height')
WHERE NOT EXISTS (Select * from settings where id = 1);

Error: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.

I can't speak for MSSQL but on Postgres there's no WHERE clause for INSERT statements. — Daniel Lyons

swasheck swasheck · Accepted Answer · 2013-01-30T23:55:34

Try this:

INSERT INTO settings (id, title, description)
    SELECT 1, 'imageHeight', 'Image Height'
        WHERE NOT EXISTS (SELECT 1 FROM settings WHERE id = 1);

sql server sql fiddle

postgresql sql fiddle

WHERE is a filter for results which are not typically pertinent to INSERT operations.

Inserting unique value into database PGSQL/MSSQL

1 Answers