python - 避免将重复项添加到存储在数据库中的列表的更好方法是什么

Question

对于 postgresdb 中的一个表及其其中一个列，该列是一个列表，用于存储以逗号分隔的项目。

当前填充该列表中项目的 python 代码使用 UPDATE-SET Postgres 语句。如果该项目已在列表中，它仍会添加它。

我有一个添加它的基本版本，首先从数据库中获取列表，然后仅在不存在时添加一个新列表，否则不添加。这种方法的问题是它可能会遇到竞争条件。它的替代和优化版本是什么。

这是我目前的方法，需要输入来优化它:

async def add(self, version, tag):

    get_sql = """
            SELECT tags
            FROM table_name
            WHERE id = $1

    """
    get_sql_args = [version]
    result = await self.execute_query_async(get_sql, get_sql_args, return_dataframe=True) # wrapper function on psycopg

    if result.iloc[0]['tags'] is None or tag not in result.iloc[0]['tags']:
        sql = """
            UPDATE table_name
            SET tags = COALESCE(tags, '[]'::jsonb) || $1
            WHERE id = $2
            RETURNING id, tags
        """
        sql_args = [tag, version]
        return await self.execute_query_async(sql, sql_args, return_dataframe=True)
    raise InvalidRequest('tag is already present.')

Answer 1

为避免竞争条件，您可以编写一个更新查询，在添加新元素之前检查它是否已存在于 JSONB 数组中。如果该值已存在，则查询不会添加它。

在 Postgres 中，您可以使用 the ? operator检查数组中是否存在字符串。

我建议更改您的逻辑，以便您传递一个字符串以将其作为字符串而不是数组(如您当前所做的那样)添加到数组中。这允许您在以下查询中重用 $1 占位符:

UPDATE table_name
SET tags = COALESCE(tags, '[]'::jsonb) || jsonb_build_array($1)
WHERE id = 1 AND NOT COALESCE(tags, '[]'::jsonb) ? $1;
RETURNING id, tags

Demo on DB Fiddle :

--set up
CREATE TABLE table_name(id int primary key, tags jsonb);
INSERT INTO table_name(id) values(1);

-- first update
UPDATE table_name
SET tags = COALESCE(tags, '[]'::jsonb) || jsonb_build_array('foo')
WHERE id = 1 AND NOT COALESCE(tags, '[]'::jsonb) ? 'foo';

--table content
SELECT * FROM table_name;

| id  | tags     |
| --- | -------- |
| 1   | ["foo"]  |

-- attempt to update with the same id and tag element
UPDATE table_name
SET tags = COALESCE(tags, '[]'::jsonb) || jsonb_build_array('foo')
WHERE id = 1 AND NOT COALESCE(tags, '[]'::jsonb) ? 'foo';

-- existing tag element was not added
SELECT * FROM table_name;

| id  | tags     |
| --- | -------- |
| 1   | ["foo"]  |