Suppose there is a simple table:

id | some_fk | value -------------------- 1 | 1 | 3

I insert data into it in this way:

 insert into table (some_fk, value) values (1, 3) returning id

How can we disable the insertion of an entry using the DBMS, if for some_fk such a value already exists?

Ie in this case, for example, values (1, 4) can be inserted, and values (1, 3) cannot, since there is already an entry with such values.

I cannot change the database structure. Just read and write data to the table.

  • table structure can be changed? look at the uniqueness constraint w3resource.com/PostgreSQL/unique.php - Novitskiy Denis
  • Yes, that would be a good decision, but under the structure of the database, I meant the structure of the tables, too - Oleg

1 answer 1

Right

Add a unique constraint or build a unique index. These are extremely related two concepts, with slight differences in syntax and creation possibilities.

Rakes and bicycles

If the correct decision is impossible for some strange reason, then it is possible to jump on crutches around at a price of noticeable overhead costs.

 insert into tablename (some_fk, value) select 1, 4 where not exists (select 1 from tablename where some_fk = 1 and value = 4) returning id

The approach is simple, but due to the visibility of transactions or race condition may give an incorrect result in the end. Therefore, it is necessary in the transaction to first acquire the appropriate lock on the table. The SHARE ROW EXCLUSIVE lock will be the minimum suitable, which will wait for all parallel transactions to modify the data in this table:

 begin; lock table tablename in SHARE ROW EXCLUSIVE mode; insert into tablename (some_fk, value) select 1, 4 where not exists (select 1 from tablename where some_fk = 1 and value = 4) returning id; commit;

Intentionally blocking any changes in the table for the entire duration of the transaction, instead of performing a check of the uniqueness of only conflicting transactions, can, of course, degrade system performance quite significantly. Therefore, you should not use this method without a strong reason.

Bicycle number 2

If you are visibly lucky, and you can control all the write operations to this table, then you can use advisory locks . The idea is the same, but instead of a large lock table request a custom lock with some kind of key.

 begin; select pg_advisory_xact_lock( /* выберите ключ из одного bigint или двух int для этой задачи, имеет смысл в каком-то виде использовать желаемое значение some_fk */ ); insert into tablename (some_fk, value) select 1, 4 where not exists (select 1 from tablename where some_fk = 1 and value = 4) returning id; commit;

But I repeat - it will work only if you can guarantee that only you yourself write values ​​to the table.

If neither the unique restriction of the lock table nor the guarantee that the entire record is carried out only by you cannot be obtained - you will have doubles or false positives will occur to check the existence of the string. Just a request with the not exists part, perhaps most, will cover, but will not be the guarantor.

  • Thanks for the chic answer, it's a pity you can not plus a few times. - Oleg
  • I correctly understand that here this select ... where not exists ... in the first two bikes you still have to wrap in values(...) ? - Oleg
  • I apologize. I see that is not correct. - Oleg
  • This is a special case of insert ... select ... syntax, that is, insert ... returning is separate, and inside there can be absolutely any select, the result of which will be used as the values ​​for insert - Shallow