How can I use GREATEST and LEAST in a Rails migration?

Question

I need to ensure bi-directional uniqueness within a table friendships on the columns requester_id and accepter_id.

Table: friendships

requester_id | accepter_id
--------------------------
           1 |           2
           2 |           1  <-- this should not be possible

As a solution to consider, I found this approach: Postgresql enforce unique two-way combination of columns

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

I am trying to write a Rails migration (I know I could use plain SQL as well but I would like to have it clean).

This is my attempt to rewrite the command. It does not work.

class AddBidirectionalUniqueConstraintToFriendships < ActiveRecord::Migration
  def change
    add index :friendships, greatest[:requester_id, :accepter_id], least[:requester_id, :accepter_id], unique: true
  end
end

Answer 1

You can run arbitrary SQL commands using an execute command.

For example:

class AddBidirectionalUniqueConstraintToFriendships < ActiveRecord::Migration
  def up
    execute <<-SQL
      create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));
    SQL
  end

  def down
    execute <<-SQL
      drop index ifriendz;
    SQL
  end
end

```