How to fix "syntax error at or near "$1" with asyncpg API

I'm trying to insert value in a table of a postgresql database by using query arguments via asyncpg API. I created my table like this :

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

I already tried to directly cast the argument in BIGINT like this : $1:BIGINT but I get the same error

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 is a int and roles a int array

It is supposed to insert cat and roles into my_table but I just got the error : syntax error at or near "$1"

I provide the logs of the database just in case

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);

Solution

You need a primary key (or unique columns) to use ON CONFLICT, so you have to define the table as

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

The column roles is ambiguous in UPDATE, fix it specifing the table name:

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)

Note that the function array_append() appends an element to an array. You can use array_cat() instead. However, it may lead to duplicated elements in a single array. If your aim is to have distinct elements in arrays, you should define a custom function in 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$;

and use it instead of array_cat().