2

I am inserting data into a table looks like this

|   num   | name     |  value |
----------------------------------
|    1    | name1    |   1    |
|    2    | name2    |   1    |
|    3    | name3    |   1    |
|    4    | name4    |   2    |
|    5    | name5    |   3    |

I wanted to insert with where clause like insert into table (num, name, value) values(6,name,1) when (num and value together) not exist in any row together

I tried to select first and insert on basis of that result but I think that is not the best way I want it in a single query

tried like: select * from the table where name=$name and value= $value if I got result then not insert otherwise insert. It was done with two queries but i don't want it.

Any help will be appriciated.

bala
  • 125
  • 6

1 Answers1

4

Use a unique constraint to enforce uniqueness for (num, value):

alter table t add constraint unq_t_num_value unique (num, value);

Then the database ensures that the integrity of the table -- that these values are unique. You don't have to do it explicitly.

Note that if the unique constraint is violated, you get an error and the insert is aborted (along with other rows that might be inserted). If you want to ignore the error instead, you can use on conflict ignore.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • what if try to insert and violate there uniqueness. other rows which have not unique value will be inserted or not and if there will be an error how can I get inserted id i wanted a simple query without making uniqueness can you please tell me @Gordon Linoff – bala Apr 15 '20 at 14:12
  • @bala . . . As pointed out in the answer, you can use `on conflict` to ignore errors. – Gordon Linoff Apr 15 '20 at 14:23
  • there is a problem .... autoincrement incremented either it inserted or not. I mean on the conflict in not inserting but autoincrement increases @Gordon Linoff – bala Apr 15 '20 at 15:01