I would advise writing the query as:
INSERT INTO sample (id, name)
SELECT id, name
FROM (SELECT 12 as id, 'rao' as name) x
WHERE NOT EXISTS (SELECT 1 FROM sample s WHERE s.id = x.id);
This means that you do not need to repeat the constant value -- such repetition can be a cause of errors in queries. Note that I removed the single quotes. id
looks like a number so treat it as a number.
The uniqueness of id
s is usually handled using a unique constraint or index:
alter table sample add constraint unq_sample_id unique (id);
This makes sure that the database ensures uniqueness. Your approach can fail if two insert
s are run at the same time with the same id. An attempt to insert a duplicates returns an error (which the exists
can then avoid).
In practice, id
columns are usually generated automatically by the database. So the create table
statement would look more like:
id integer generated by default as identity
And the insert would look like:
insert into sample (name)
values (name);