pg_auto_parameterize_in

The pg_auto_parameterize_in_array extension builds on the pg_auto_parameterize extension, adding support for handling additional types when converting from IN to = ANY and NOT IN to != ALL:

DB[:table].where(column: [1.0, 2.0, ...])
# Without extension: column IN ($1::numeric, $2:numeric, ...) # bound variables: 1.0, 2.0, ...
# With extension:    column = ANY($1::numeric[]) # bound variables: [1.0, 2.0, ...]

This prevents the use of an unbounded number of bound variables based on the size of the array, as well as using different SQL for different array sizes.

The following types are supported when doing the conversions, with the database type used:

Float	if any are infinite or NaN, double precision, otherwise numeric
BigDecimal	numeric
Date	date
Time	timestamp (or timestamptz if pg_timestamptz extension is used)
DateTime	timestamp (or timestamptz if pg_timestamptz extension is used)
Sequel::SQLTime	time
Sequel::SQL::Blob	bytea

Arrays of string values are not automatically converted by default, because the Ruby String class can represent a number of different database types. To convert arrays of Ruby strings to an untyped array (a query parameter with no explicit type cast), set the :treat_string_list_as_untyped_array Database option before loading the extension.

If you will only be using arrays of Ruby strings that represent the text type, you can use the :treat_string_list_as_text_array Database option is used. This can break programs, since the type for literal strings in PostgreSQL is unknown, not text.

The conversion is only done for single dimensional arrays that have two or more elements, where all elements are of the same class (other than nil values). You can also do the conversion for arrays of 1 element by setting pg_auto_parameterize_min_array_size: 1 Database option. This makes finding cases that need special handling easier, but it doesn’t match how PostgreSQL internally converts the expression (PostgreSQL converts IN (single_value) to = single_value, not = ANY(ARRAY[single_value])).

pg_auto_parameterize_in_array.rb