Awesome Awesomeness
I just came across this curated list of awesome curated lists! So meta, I love it! https://github.com/bayandin/awesome-awesomeness https://awesome-awesomeness.zeef.com/alexander.bayandin …
Use the timestamptz shorthand for time zones in Postgres
The following CREATE TABLE statements are equivalent: CREATE TABLE users ( id serial PRIMARY KEY, username text NOT NULL, created_at timestamptz ); CREATE TABLE users ( id serial PRIMARY KEY, username text NOT NULL, created_at timestamp…
Porting ActiveRecord "soft delete" behavior to Postgres
Let's try to implement the behavior of libraries like paranoia and acts_as_paranoid in Postgres! tldr - jump to the summary below! We'll make it possible to restore deleted user accounts! Let's assume that…
Porting ActiveRecord "counter cache" behavior to Postgres
Let's try to mimic the following belongs_to relationship with counter_cache behavior in Postgres! tldr - jump to the summary below! class Comment < ActiveRecord::Base belongs_to :post, counter_cache: true end class…
Porting ActiveRecord validations to Postgres
Let's try to port some ActiveRecord validations to Postgres using constraints! validates_presence_of class User < ActiveRecord::Base validates_presence_of :email end ALTER TABLE users ALTER COLUMN email SET NOT NULL; ALTER TABLE…
Postgres treats table rows like composite types
Let's create a basic users table with sample data: CREATE TABLE users ( id serial PRIMARY KEY, name text ); INSERT INTO users ('name') VALUES ('Bob'), ('Tom'), ('Sam'); Then SELECT * FROM users to see what we're working…
Case insensitive UNIQUE constraints in Postgres
Adding UNIQUE constraints to tables in Postgres is very easy! Imagine we have the following table: CREATE TABLE users ( id uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(), email text ); If we want to…
Parsing tags from text content in Postgres
While experimenting with building a simple Twitter clone in Postgres, I found that I needed a way to parse hashtags and mentions from tweets like: #example tweet - #testing with @postgresql Imagine that we have…
Silently drop everything in Postgres
Sometimes when working in Postgres I like to reset my development database by deleting everything in it without dropping the actual database itself. An easy way to achieve this is to just drop all database…
Reading from the filesystem with Postgres
Let's try to make the following SQL statement work: SELECT file.read('/tmp/test.txt'); We can start by creating the file /tmp/test.txt with the following contents: Hello PostgreSQL! The next step…
Using the uuid data type in Postgres
It's incredibly simple to use the uuid data type in PostgreSQL! First we need to enable the uuid-ossp extension by executing the following SQL: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; Now the uuid data type…
Enumerable#each_with_object
The Enumerable#each_with_object method behaves very similarly to Enumerable#inject! There are only a couple of differences! The block arguments are reversed The inject method passes the block arguments in the following order…
Return random records in Postgres
Lately I've been experimenting with building a simple "twitter" app in Postgres by pushing as much logic in the database layer as possible. This includes data validation/sanitization, triggers, and functions. I needed a way…
I wish this was valid Ruby
Valid Ruby (the only valid line of code in this post) before_validation(on: :create) { contact.email ||= email } Drop braces and use a stabby proc before_validation(on: :create) -> contact.email ||= email Drop…
Docker
tldr; Let's get started I've heard a lot about Docker over the past year but haven't really dived into it much until recently. So far the experience has been amazing! I've found the technology to…