Good article about window functions. Also note how they use a postgres feature called common table expressions.

The linked article explains how to get a database-wide lock without creating table rows: This article explains how I...

Postgres 9.4 introduces a new column type: jsonb. json and jsonb columns store data differently, so just compare the two...

Sequel is an awesome ORM such as ActiveRecord. The linked article describes how easily you can implement and use materialized...

In the tradition of our PostgreSQL cheat sheet for MySQL lamers, here is a cheat sheet for Jasmine when you...

A CLI for working with Postgres databases. Ships with auto-completion and syntax highlighting.

Rails guide that covers PostgreSQL-specific column types and usages for Active Record. You should especially keep in mind the...

SELECT enum_range(NULL::portal) # Returns an array of all possible values SELECT unnest(enum_range(NULL::portal)) # Unnests the...

class Document < ActiveRecord::Base scope :any_tags, -> (tags){ where('tags && ARRAY[?]', tags) } scope :all_tags, -> (tags){ where('tags @> ARRAY...

If you get an error like this: An error occurred while installing pg (0.17.1), and Bundler cannot continue.

By default, Rails' validates_uniqueness_of does not consider "username" and "USERNAME" to be a collision. If you use MySQL...

Like in any language, a FLOAT will eventually corrupt data due to rounding errors. Please use DECIMAL, which has well...

When writing Rails migrations to convert a string column to an integer you'd usually say: change_column :table_name...

There may be reasons to change the locale of your Postgres cluster. A popular one is your development system's...

So you're switching to PostgreSQL from MySQL? Here is some help... General hints on PostgreSQL \? opens the command overview...

Rails gives you migrations to change your database schema with simple commands like add_column or update. Unfortunately these commands...

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

validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in...

For string columns, MySQL indexes the left side of a string. That means an index can speed a like query...

When requests arrive at the application servers simultaneously, weird things can happen. Sometimes, this can also happen if a user...

An association defined with has_many :through will return the same record multiple times if multiple join models for the...

In MySQL comparing zero to a string 0 = "any string" is always true! So when you want to compare a...

When selecting records in a date range, take care not to do it like this: start_date = Date.parse('2007-05...

Note: This applies specifically to MySQL. In PostgreSQL for example, this is not an issue. If you care about performance...