Masatoshi Nishiguchi

PostgreSQL memo

PostgreSQL memo.

Outside psql

Create a new database

$ createdb moma_db

List all the databases

$ psql -l

                                          List of databases
               Name                | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------------------------------+-------+----------+-------------+-------------+-------------------
 gladiator                         | masa  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 hello_app_1_development           | masa  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Column indexing and unique column constraints in Rails

This is my memo on Column indexing and unique column constraints in Rails.

Column indexing

# # Add an index on the email column of the users table.
$ rails generate migration add_index_to_users_email

# The migration for adding an index on the email column of the users table and
# enforcing email uniqueness.
# Note: The index by itself doesn’t enforce uniqueness.
# (The option `unique: true` does.)
# https://www.railstutorial.org/book/modeling_users#code-email_uniqueness_index
class AddIndexToUsersEmail < ActiveRecord::Migration
  def change
    add_index( :users, :email, unique: true )
  end
end

# NOTE: When you tell Rails something like `belongs_to :photo`, Rails does not
# update the database to index `user.photo_id`.
# This way, each direction of our user and photo lookup is fast and easy.
def up  
  add_index( :users, :username )
  add_index( :users, :photo_id )
end

Reference