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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…