Feedback-ID: i76984098:Fastmail
Message-ID: <acfaa89e-fd23-4318-b946-645e6e0b1749@aklaver.com>
Date: Sat, 7 Dec 2024 10:32:23 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Insert records in the tavke only if they are not exist
To: Igor Korot <ikorot01@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CA+FnnTwcNNGsM_ck0ye1OsUN3Jjg94PzGMKRm--2ojJM_zVOMA@mail.gmail.com>
 <8770b693-0c44-40de-b883-36cc5e718a0a@aklaver.com>
 <CA+FnnTy7w3jaZCOuMGMMNGySfiTjcHh5D06AUMc+w+i6yoKrMA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+FnnTy7w3jaZCOuMGMMNGySfiTjcHh5D06AUMc+w+i6yoKrMA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/acfaa89e-fd23-4318-b946-645e6e0b1749%40aklaver.com>
Precedence: bulk

On 12/7/24 09:59, Igor Korot wrote:
> Hi, Adrian,
> 
> On Sat, Dec 7, 2024 at 11:18 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 12/6/24 19:36, Igor Korot wrote:
>>> Hi, All,
>>>
>>> When my application starts up, ot creates some tables and insert records
>>> in them.
>>>
>>> When the app starts for the second time it should check if the tables
>>> and the records in them are exist and skip the process.
>>
>> Does this also apply to starts after the second time?
> 
> Yes, it does.
> 
> However, if the new table is created in the meantime - the new record
> should be created, because it is not there yet.

What determines that a new table needs to be created and populated?

> 
>>
>> Is the data expected to change over time?
> 
> Data change is possible.
> 
> This is one of the tables:
> 
>      "CREATE TABLE IF NOT EXISTS \"sys.abcatfmt\"(\"abf_name\" char(30)
> NOT NULL, \"abf_frmt\" char(254), \"abf_type\" smallint, \"abf_cntr\"
> integer" ));";
>      "CREATE UNIQUE INDEX IF NOT EXISTS pbcatf_x ON
> \"sys.abcatfmt\"(\"abf_name\" ASC);";
> 
> My understanding is that "INSERT OR IGNORE" will check the unique

It is INSERT ... ON CONFLICT DO NOTHING | UPDATE.

> index and will not do anything if the recrd
> with such data on the index already exists.

So you have the option of either skipping the insert or updating 
selected fields in the row.

> 
>>
>>>
>>> Everything is good, except what if I have a connection from 2 different
>>> users?
>> Is it possible to have the app start up without external users connecting?
> 
> Not sure what you mean here.
> 
> Are you asking if the computer can run the app?
> Then the answer is NO. App is always started by the user.
> Please clarify.

This is going to need a more detailed explanation of what 'app' means.

Are you talking about the front end that the user launches or the 
backend that runs the database or something else?


> 
> Thank you.
> 
>>
>>>
>>> I can run this inside transaction, but will this be enough? Will stating
>>> transaction lock the DB and the second user will wait for transaction to
>>> complete?
>>>
>>> Thank you.
>>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>

-- 
Adrian Klaver
adrian.klaver@aklaver.com