Concat String in PostgreSQL

Recently, I learnt that you can concatenate string in PostgreSQL using || instead of relying of CONCAT function. For example,

SELECT firstname || ' ' || lastname
FROM users;

Git Patches

You may be familiar with git stash, but patches can also be very useful. Assuming you have changes to the working directory, instead of stashing it, we can create a patch:

git diff > example.patch

With that, we have created a patch file that contains the current diff. Now, instead of stashing, we can just discard all changes. To apply the patch, just do:

git apply example.patch

This is similar to running git stash pop to update the current working directory with the diff from example.patch. With patches, it’s easier to keep track of what each patch does. On top of that, you can distribute patches to other people so they can quickly apply the change to their repository. It’s super useful to create patches for testing a feature for example. Your reviewer can then just apply the patch when they’re reviewing your branch.

To revert the patch, simply do:

git apply -R example.path

You can use either -R or --reverse.

Tags: git

Get the first/last ordered record with Ecto

When using Ecto in Elixir/Phoenix application, there are a few approaches we can use to fetch the first record ordered by specific column.

Using limit and order_by

We can use a combination of Ecto.Query.limit, Ecto.Query.order_by and to get the first record:

query = from p in Post, order_by: :id, limit: 1

Depends on the requirement of ordering, we can also use order_by: [desc: :id] to sort the table in descending order and get the first result.

Using first or last

The above approach can be quite verbose. Fortunately, Ecto.Query also have first() and last() function that restrict the query to return the first or last result ordered default by primary key.

Both function will automatically set the query limit to 1. We can also provide order_by parameter these functions to specify the column to be used for ordering.

Post |> first() |>
Post |> first(:views) |>

For more details on the functions, you can refer to:

Using raw SQL in Ecto

To use raw SQL with ecto, we can use Repo.query. For example:

Repo.query("SELECT * from users;"

# Returns
   columns: ["id", "email"],
   command: :select,
   connection_id: 73530,
   messages: [],
   num_rows: 1,
   rows: [
     [1, "[email protected]"]

You might be wondering that why you can’t find Repo.query in the Ecto.Repo documentation.

It is actually documented under Ecto.Adapters.SQL documentation since this function is implemented in Ecto.Adapters.SQL and is make available to repository module for convenience.

Tags: ecto

Formatting JSON in Vim

You probably know that we can use something like :%!python -m json.tool to format a JSON file that’s opened in Vim. However, I always find this command hard to recall. Instead of dedicating a mapping for this, we can actually rely on equalprg instead. Let’s set this autocmd in .vimrc:

augroup vimrc
  autocmd FileType json setlocal equalprg=python\ -m\ json.tool
augroup END

Now, when we open a JSON file, we can use the familiar = key to format it nicely.