CODEWITHSUNDEEP
sqlpostgresql
bala
bala

Reputation: 125

How can i insert into table on the basis of a column value is same or not in Postgresql

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.

Upvotes: 2

Views: 192

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 4

Related Questions