PostgreSQL’s between predicate

Use between/3 to perform a range test for the first argument against the second (lower bound) and third argument (upper bound). Returns true if the value falls in the given range. False otherwise.

from(posts in "posts",
select: {posts.title, posts.description}
where: between(posts.published_at,
               ^Ecto.DateTime.cast!({{2016,5,10},{0,0,0}}),
               ^Ecto.DateTime.cast!({{2016,5,20},{0,0,0}})))

PostgreSQL’s coalesce function

Use coalesce/1 to return the first value in the given list that is not null.

from(posts in "posts",
select: {
  posts.title,
  coalesce([posts.short_description, posts.description, "N/A"])
})

PostgreSQL’s coalesce function

Use coalesce/2 to return the first argument that is not null.

from(posts in "posts",
select: {
  posts.title,
  coalesce(posts.short_description, posts.description)
})

PostgreSQL’s greatest function

Use greatest/1 to return the largest of a list of arguments. This function will always preference actual values over null.

from(posts in "posts",
select: greatest([
                   posts.created_at,
                   posts.updated_at,
                   posts.published_at
                 ]))

PostgreSQL’s greatest function

Use greatest/2 to return the larger of two arguments. This function will always preference actual values over null.

from(posts in "posts",
select: greatest(posts.created_at, posts.published_at))

PostgreSQL’s least function

Use least/1 to return the smallest of the arguments. This function always preferences actual values over null.

from(posts in "posts",
select: least([
                posts.created_at,
                posts.updated_at,
                posts.published_at
              ]))

PostgreSQL’s least function

Use least/2 to return the smaller of the two arguments. This function always preferences actual values over null.

from(posts in "posts",
select: least(posts.created_at, posts.updated_at))

PostgreSQL’s lower function

Use lower/1 to lowercase a given string. This works like Elixir’s String.downcase/1 function allowing string manipulation within a query.

from(users in "users",
select: lower(users.email))

PostgreSQL’s not between predicate

Use not_between/3 to perform a range test for the first argument against the second (lower bound) and third argument (upper bound). Returns true if the value does not fall in the given range. False otherwise.

from(posts in "posts",
select: {posts.title, posts.description}
where: not_between(posts.published_at,
                   ^Ecto.DateTime.cast!({{2016,5,10},{0,0,0}}),
                   ^Ecto.DateTime.cast!({{2016,5,20},{0,0,0}})))

PostgreSQL’s nullif function

Use nullif/2 to return null if the two arguments are equal.

from(posts in "posts",
select: nullif(posts.description, ""))

This is a peculiar function, but can be handy in combination with other functions. For example, use it within coalesce/1 to weed out a blank value and replace it with some default.

from(posts in "posts",
select: {
  posts.title,
  coalesce(nullif(posts.description, ""), "N/A")
})

PostgreSQL’s upper function

Use upper/1 to uppercase a given string. This works like Elixir’s String.upcase/1 function allowing string manipulation within a query.

from(users in "users",
select: upper(users.username))