PostgreSQL ARRAY

pg_arrays.rb

# migrate
add_column :doges, :friends, :text, array: true, default: []

# add an index
add_index :doges, :friends, using: :gin

# create a Doge
Doge.create(name: "Doge", friends: ["snoop", "snoopy"])

# any Doge contains a friend snoop
Doge.where("friends @> array [?]", "snoop")

# ignore empty friends to speedup queries
Doge.where("friends <> '{}'").where("friends @> array [?]", "snoop")

# unique friends
Doge.where("friends <> '{}'").pluck("DISTINCT(UNNEST(friends))")

pg_arrays.sql

CREATE TABLE doges (
  id integer,
  name text,
  friends text[] DEFAULT '{}'::text[]
);

# doges with friends containing "snoop":
SELECT * FROM doges WHERE (friends @> ARRAY['snoop']);

# uniq friends in all doges
SELECT DISTINCT(UNNEST(friends)) FROM doges WHERE (friends <> '{}');