Can't insert integer array in PostgreSQL

[timothyqiu]

PostgreSQL (psycopg2 driver) uses list to pass integer array type (integer[]), but pugsql expands list params not only for the IN clause, but also in other places: https://github.com/mcfunley/pugsql/blob/b41998717d33e9d9b0319602e848dbd26057baca/pugsql/statement.py#L26-L38

For example:

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    bar INTEGER[] NOT NULL
);

and the SQL:

-- :name create :scalar
INSERT INTO foo (bar) VALUES (:bar)

• queries.create(bar=[1, 2]) yields: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "bar" is of type integer[] but expression is of type record
• queries.create(bar=[1, ]) yields: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "bar" is of type integer[] but expression is of type integer

It works fine if I comment out line 29-31 in the source code above.

[mcfunley]

Ah interesting, so if i understand correctly our _visit_bindparam is getting in the way of this just working with the underlying sqlalchemy, because we're just naively testing if the value is iterable?

Good catch, I probably won't have time to dig for a while but it seems like you're pretty deep into investigating. Any ideas on how to fix?

[timothyqiu]

A proper fix might be expanding the parameter only for IN clauses, but it seems hard to implement.

My current workaround is changing the column type from integer[] to jsonb, so that I can call the function with psycopg2.extras.Json([1, 2, 3]) to save the integer array. Maybe PugSQL can also introduce a wrapper like this, e.g. ArrayLiteral, so that queries.create(bar=ArrayLiteral([1, 2])) passes the list as is.

[mcfunley]

Yeah that's a potentially good approach. I've gone down the path of parsing the sql a few times and thought better of it every time.