There is a table of this type:

Table "public.project" Column | Type | Modifiers | Storage | Stats target | Description ---------------+---------+-----------+----------+--------------+------------- id | bigint | | plain | | custom | hstore | | extended | |

In the custom field, I want to write the value, without overwriting the values ​​that are already contained in this field, in this way:

 UPDATE "project" set "custom" = "custom" || ('"testKey"=>"testValue"') where "id"=1;

But if there was no data in this field before, i.e. I write to it for the first time, this request does not work. In order for it to work it is necessary to make a request first in this form (this request overwrites the existing data when it is called again).

 UPDATE "project" set(custom) = ('"testKey" => "testValue"') where "id"=1;

And only then can I write the data in the first way.

It turns out that when you first write data, I need one sql query, and on subsequent ones, another one.

Question : how do I know what I am writing in the field for the first time to determine what type of sql I need to use right now?

    1 answer 1

    Obviously you have NULL default, and concatenation NULL with hstore gives NULL. It is enough to replace the NULL request with an empty hstore via coalesce:

     UPDATE "project" set "custom" = coalesce("custom", '') || ('"testKey"=>"testValue"') where "id"=1;
    • Super, it works, for some reason I didn’t notice this when I read the documentation, can you drop the link where I can read about it? - Kirill Stoianov
    • What exactly? Features of concatenation with null? - Shallow
    • yes, concatenation with null! - Kirill Stoianov
    • Direct text seems to be not written. It is checked elementary. In general, many NULL operations are NULL. Because it is NULL - i.e. this is unclear. It is not clear that plus 3 it will still be unclear what - Minor