Adding and updating records via Insert Into?

Question

PostgreSQL

Hello. Question to connoisseurs, both in terms of performance and correctness. For example, there is a table

ROLLBACK;
BEGIN;
CREATE TEMP TABLE test( id integer, number varchar(12), other varchar(10)) ON COMMIT DROP;
CREATE UNIQUE INDEX ON test( number );
insert into test values ( 1, '1', '');
select * from test;
commit;

For example, there is an insertion by a unique number (number field)
1) First, we look for whether there are records and if the request returned nothing, insert the record if it returned, then update the records 2) Or is it better to do everything with one request
select * from test where number = '1';
insert into test values ( '1', '');
update test set other = 'ttt' where number = '1';

insert into test values ( '1', 'tttt') ON CONFLICT (number) DO update SET other = EXCLUDED.other;

Of course, I like the second option better: less code + fewer table calls + easier to maintain, but how much can it be used in large projects? Thanks

Answer 1

With insert or update on the result of select (by the way, a completely unnecessary request), you get into a race condition
on conflict. It is specially made for the normal serialization of what is happening and should be used. Other ways to serialize a race condition from times before on conflict squander performance for obvious reasons.