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:

pugsql/pugsql/statement.py

Lines 26 to 38 in b419987

	@compiles(BindParameter)
	def _visit_bindparam(element, compiler, **kw):
	cc = getattr(_locals, 'compile_context', None)
	if cc:
	if _is_expanding_param(element, cc):
	element.expanding = True
	return compiler.visit_bindparam(element)
	def _is_expanding_param(element, cc):
	if element.key not in cc['params']:
	return False
	return isinstance(cc['params'][element.key], (tuple, list))

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.