如何使用asyncpg API修复“$1”处或附近的“语法错误”

Question

我试图通过asyncpgapi使用查询参数在postgresql数据库的表中插入值。 我创建了这样的表：

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

我已经尝试过像这样在BIGINT中直接转换参数：$1:BIGINT，但是我得到了相同的错误

^{pr2}$

cat是int，roles是int数组

它应该将cat和roles插入到我的表中，但是我得到了错误：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中不明确，请通过指定表名修复它：

^{pr2}$

注意，函数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()。在