Как исправить "синтаксическую ошибку на уровне или около $1" с помощью asyncpg API

Question

Я пытаюсь вставить значение в таблицу базы данных postgresql, используя аргументы запроса через asyncpg API. Я создал свою таблицу следующим образом:

CREATE TABLE my_table
(
    cat BIGINT,
    roles BIGINT ARRAY
);

Я уже пытался напрямую привести аргумент в BIGINT так: $1:BIGINT но я получаю ту же ошибку

await connection.execute('''
        INSERT INTO my_table(cat, roles)
        VALUES($1, $2)
        ON CONFLICT ($1)
        DO UPDATE SET roles = array_append(roles, $2)
        ''', cat, roles)

cat - это int, а роли - массив int

Предполагается вставить cat и ролей в my_table, но я только что получил ошибку: syntax error at or near "$1"

Я предоставляю журналы базы данных на всякий случай

2019-01-26 21:01:22 UTC:172.31.36.115(37598):Barbote@Barbotedb:[15082]:ERROR: syntax error at or near "$1" at character 111
2019-01-26 21:01:22 UTC:172.31.36.115(37598):Barbote@Barbotedb:[15082]:STATEMENT: 
INSERT INTO "429792212016955423"(cat, roles)
VALUES($1 $2)
ON CONFLICT ($1)
DO UPDATE SET roles = array_append(roles, $2);

Answer 1

Вам нужен первичный ключ (или уникальные столбцы), чтобы использовать ON CONFLICTтак что вы должны определить таблицу как

CREATE TABLE my_table
(
    cat BIGINT PRIMARY KEY, -- !!
    roles BIGINT ARRAY
);

Колонка roles неоднозначно в UPDATE, исправьте это, указав имя таблицы:

await connection.execute('''
        INSERT INTO my_table(cat, roles)
        VALUES($1, $2)
        ON CONFLICT (cat)
        DO UPDATE SET roles = array_cat(my_table.roles, $2)
        ''', cat, roles)

Обратите внимание, что функция array_append() добавляет элемент в массив. Ты можешь использовать array_cat() вместо. Однако это может привести к дублированию элементов в одном массиве. Если ваша цель состоит в том, чтобы иметь отдельные элементы в массивах, вы должны определить пользовательскую функцию в Postgres:

create or replace function public.array_merge(anyarray, anyarray)
returns anyarray language sql
as $function$
    select 
        array(
            select unnest($1)
            union
            select unnest($2)
            order by unnest
        )
$function$;

и использовать его вместо array_cat(),