skip to content

In a Rails 8.1 app on PostgreSQL, how does an array: true column for sneaker sizes behave in Ruby and in where queries?

level: middleimportance: should knowfreq 40%

answer

  1. add_column ... array: true, default: []
  2. each element cast to the column type
  3. where(sizes: [...]) means whole-array equality
  4. ANY or @> in a SQL fragment
  5. << followed by save is detected

basics

~20 s

array: true makes a PostgreSQL array column that Active Record reads as a Ruby Array, casting each element to the column's type. where(sizes: ["9", "10"]) matches only that exact array; membership needs a SQL fragment with ANY or @>.

solid answer

~40 s

`t.string :sizes, array: true, null: false, default: []` creates a `character varying[]` column. Active Record casts each element through the column's type, so assigning `[9, 9.5]` to a string array stores `["9", "9.5"]`, and in-place edits such as `sneaker.sizes << "10"` count as a change that `save` writes. Querying is where people slip: because the array type forces equality, `Sneaker.where(sizes: ["9", "10"])` means the column equals exactly that array, not "has size 9 or 10". Membership goes in a fragment: `where("? = ANY(sizes)", "10")` for one size, or `where("sizes @> ARRAY[?]::varchar[]", ["9", "10"])` for all of several, which `add_index :sneakers, :sizes, using: :gin` can serve.

code

ruby · 8 lines
ruby
sneaker = Sneaker.create!(name: "Air Max 1", sizes: [9, 9.5])
sneaker.sizes              # => ["9", "9.5"]
sneaker.sizes << "10"
sneaker.save!              # in-place change is written

Sneaker.where(sizes: ["9", "10"])                         # exact array only
Sneaker.where("? = ANY(sizes)", "10")                     # has size 10
Sneaker.where("sizes @> ARRAY[?]::varchar[]", ["9", "10"]) # has both

go deeper

for a junior

Recall array: true with default: [], that you get a Ruby Array, and that elements are cast to the column type.

for a middle

Explain why a Ruby Array in where means equality on an array column, and write the ANY and @> fragments with binds.

for a senior

Index with GIN for containment and overlap, and spot when an array should become a has_many table.

for a principal

Judge where arrays simplify a schema and where they quietly give up integrity and concurrent updates.

## Declaring an array column PostgreSQL lets any column hold an array of its type, and Rails exposes that with the `array: true` option on any column method: ```ruby create_table :sneakers do |t| t.string :name, null: false t.string :sizes, array: true, null: false, default: [] t.timestamps end add_index :sneakers, :sizes, using: :gin ``` For a sneaker marketplace, `sizes` lists the sizes a seller has in stock. The SQL type becomes `character varying[]`. `default: []` with `null: false` saves every caller from checking for `nil` before calling `include?` or `<<`. ## What Active Record does with the values The PostgreSQL adapter wraps the element type in an array type that: - **casts each element** with the column's own type, so `sneaker.sizes = [9, 9.5]` on a string array becomes `["9", "9.5"]`, and an integer array would turn `"42"` into `42`; - **parses strings in PostgreSQL's array literal form**, so `"{9,10}"` assigned from a form or CSV becomes `["9", "10"]`; - **detects in-place changes**: `sneaker.sizes << "10.5"` or `sneaker.sizes.delete("9")` followed by `save` writes the new array, because the type compares the stored value with the current one. The schema dump records the option, so `db/schema.rb` shows `t.string "sizes", default: [], null: false, array: true`. ## Querying: the equality trap The array type tells Active Record's predicate builder to **force equality** whenever the value is a Ruby Array. So: ```ruby Sneaker.where(sizes: ["9", "10"]) ``` builds `WHERE sizes = '{9,10}'` and returns only listings whose sizes are exactly 9 and 10, in that order. It is **not** an `IN` list, which is what the same call means on an ordinary column, and that difference is a favourite interview question. Membership questions go in SQL fragments, with the values bound: | Question | Rails code | |---|---| | has size 10 | `Sneaker.where("? = ANY(sizes)", "10")` | | has all of 9 and 10 | `Sneaker.where("sizes @> ARRAY[?]::varchar[]", ["9", "10"])` | | has any of 9 or 10 | `Sneaker.where("sizes && ARRAY[?]::varchar[]", ["9", "10"])` | A Ruby array bound to `?` expands to a comma-separated list, which is why it sits inside `ARRAY[...]`. The cast to `varchar[]` matches the column's element type so the operators compare like with like. How each operator treats duplicates and empty arrays is PostgreSQL's own subject. ## Indexing - A **GIN** index on the array column (`using: :gin`) serves the containment (`@>`) and overlap (`&&`) operators. - The `= ANY(sizes)` form reads well but is not served by that GIN index; for a hot query, write the one-element containment form, `sizes @> ARRAY[?]::varchar[]` with `["10"]`, which is. ## Updating arrays - **Every save rewrites the whole array.** Rails sends the complete new value in the `UPDATE`, so concurrent edits to the same listing's sizes follow last-write-wins. - **Atomic appends** use SQL through `update_all`, for example `Sneaker.where(id: id).update_all(["sizes = array_append(sizes, ?)", "11"])`, at the cost of skipping validations and callbacks. - **Duplicates and order are yours to manage.** PostgreSQL keeps elements in the order given and allows repeats, so call `uniq` (and `sort` if order matters) before assigning. ## When an array is the wrong shape 1. **Each element has its own data.** Once a size needs a quantity and a price, it is a `sneaker_sizes` table with a `has_many`, not an array. 2. **Elements refer to other rows.** PostgreSQL cannot enforce a foreign key on array elements, so an array of ids gives up referential integrity. 3. **You need to update one element concurrently.** Every change rewrites the whole array, so two sellers editing stock at once can overwrite each other. A short list of plain values that you read together and filter by membership (sizes, tags, allowed regions) is the case arrays fit.

  • Why does where(sizes: ["9", "10"]) build an IN list on a plain string column but an equality on an array column?
    The predicate builder asks the column's type whether it forces equality for the value. The PostgreSQL array type answers yes for any Ruby Array, so the whole array is bound as one value and compared with `=`. Ordinary column types answer no, so the Array handler builds `IN (...)`.
  • When would you replace a sizes array with a separate table?
    As soon as a size carries its own data, such as stock count or price, or needs to be referenced, constrained or updated concurrently. A `sneaker_sizes` table with `belongs_to :sneaker` gives foreign keys, per-row updates and validations that an array column cannot.

  • Expects where(sizes: [...]) on an array column to build an IN list
  • Interpolates sizes into the SQL string instead of binding them
  • Thinks sizes << "10" is lost on save without will_change!
  • Stores foreign-key ids in an array and expects referential integrity
  • Leaves the array nullable with no default and calls include? on nil