Should you use a Document (JSON) or Relational database to power your website? With Postgres you don't need to choose between them... the answer is to use both! Postgres 9.4 comes with amazing JSON support which gives you the best of both worlds. This article also explores how to use this new functionality from within Rails.
If you search "trgrams in pstgres" in Google, you'll get results for "trigrams in postgres". To say that is useful is an understatement! Recently at work I wanted to search a table of tags, but I wanted to account for spelling mistakes. Just because the user is 1 letter off doesn't mean I shouldn't give them a meaningful search result. We are going to use the Trigram feature in Postgres to help us achieve this.
MongoDB is an object or document based database... in other words, a NoSQL database. This puts it in contrast to databases such as MySQL, PostgreSQL, or SQLite, who store their data relationally in a tabular format. MongoDB stores its data in JSON-style documents. If you're familiar with JSON, you'll already be familiar with how MongoDB stores its data.
When we first modelled the data it made sense, but over time business requirements change and we discovered that the model made more sense to be split in half to satisfy 2 unique models instead of 1. This article is about creating a copy of the table, while preserving it's column settings and characteristics (default value, not null, etc...)
PSQL is the command line tool for accessing the PostgreSQL database. I recommend anyone using Postgres to at least learn the basics of how to navigate around and feel comfortable working in this tool. Even for those used to only dealing with data through an ORM like ActiveRecord in Rails, or for those who prefer Navicat for querying data, learning a simple tool like PSQL will go a long way. The next time you're on the server and there is no GUI in site, you'll feel comfortable instead of stressed.
Here is how to deal with null values when sorting your data in Postgres. Postgres has a convenient option to either put them at the beginning or end of your query results.