Postgresql: trigram indexes for searching

For searching in large database tables we usually use PostgreSQL's fulltext search capabilities.

While this works reasonably well for content primarily consisting of prose, it is not necessarily a good solution for all use case...

You can improve your LIKE / ILIKE search queries in PostgreSQL by adding a GIN index with an operate class ("opclass") to split the [words into trigr...

So you're switching to PostgreSQL from MySQL? Here is some help...

General hints on PostgreSQL

• \? opens the command overview
• \d lists things: \du lists users, \dt lists tables etc

Command comparison

| Description | MySQL comm...

validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in production, multiple worker processes can cause race conditions:

1. Two concurrent requests try to create a user with the same name (a...

When searching for text in a MySQL table, you have two choices:

• The LIKE operator
• [FULLTEXT](http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html...

Rails understands a :limit options when you create columns in a migration. Its meaning depends on the column type, and sometimes the supplied value.

[The documentation](http://apidock.com/rails/ActiveRecord/ConnectionAdapters/TableDefinition/co...

PostgreSQL's array data type is pretty useful, but manipulating values of arrays can be awkward because of its syntax.

Consider the following users table which each example below will sta...

PostgreSQL offers a really handy field type: json. You can store any JSON there, in any structure.

While its flexibility is great, there is no syntactic sugar in Rails yet. Thus, you need to manually query the database.

Demo

# Given...

When using ORDER BY "column" in PostgreSQL, NULL values will come last.

When using ORDER BY "column" DESC, NULLs will come first. This is often not useful.

Luckily, you can tell PostgeSQL where you want your NULLs, by saying

......

Using uncountable resources is not recommended as it breaks Rails' magic, e.g. when using form_for. You'll always be better off using simple pluralizable resources.

Rails automatically creates path names for routes defined via the `res...