[For Mac Users] Summary of PostgreSQL Commands (from Installation to Explanation)

This post is intended for those of you who are developing software on Macs

Through this post, you can acquire the basic knowledge of everything from the installation of PostgreSQL to the usage of the visual operation tool ‘pgAdmin’.

Installation of PostgreSQL

The easiest way to do this is by using brew. All you have to do is open the terminal and enter the following:

brew install postgresql
# If you want to see the version
postgres --version

Next, install ‘pgAdmin’ which allows you to operate visually. (Optional) The method of use will be described in detail later.

brew cask install pgadmin4

Initialization of PostgreSQL

You will need to initialize first. (For some reason, it was not necessary in my current environment.)

initdb /usr/local/var/postgres -E utf8

By default, your current Mac username will be added to the PostgreSQL username.

Starting and Stopping PostgreSQL

Here are the operations related to starting and stopping. I believe using brew for these operations is the easiest method as well.

# Start
brew services start postgresql
# Stop
brew services stop postgresql
# Reboot
brew services restart postgresql

# (Reference) Check the list of services running on brew
brew services list

Database Operation Commands

Command to Check the List of Databases

It’s a command to check the existing databases.

The initial state of “PostgreSQL” is shown below.

psql -l

# Below is the execution result (example)
                               List of databases
    Name    |   Owner   | Encoding | Collate | Ctype |    Access privileges    
------------+-----------+----------+---------+-------+-------------------------
 example-db | ***       | UTF8     | C       | C     | 
 postgres   | ***       | UTF8     | C       | C     | 
 template0  | ***       | UTF8     | C       | C     | =c/***      +
            |           |          |         |       | ***=CTc/***
 template1  | ***       | UTF8     | C       | C     | ***=CTc/***+
            |           |          |         |       | =c/***
 test       | ***       | UTF8     | C       | C     | 

Commands to Create and Delete Databases

# Create a new database
createdb database_name

# Delete a database
dropdb database_name

Commands Related to Tables

Command to Enter the SQL Input Reception State

psql -h hostname -p port_number -U role_name -d database_name

# In the following conditions, only database name input is OK
　　(For the current user name, host: localhost
　　　　port: default port of postgreSQL)
psql database_name

# ⬆Execution result (when 'test' is specified as the database name)
psql (13.1)
Type "help" for help.

test=# 
#  ⬆　You can enter SQL here

Command to Exit from SQL Input Reception State

\q   #  Don't append a semicolon at the end
# Shortcut ctrl−d is also OK

Commands to Check the List of Tables and View Column Information of Tables

# Check the list of tables
\dt;  # No need for a semicolon at the end is OK
# ⬆ Execution result (example)
          List of relations
 Schema |  Name   | Type  |   Owner   
--------+---------+-------+-----------
 public | mytable | table | ********



# Check the column information of a table
\d (table_name);   # e.g., \d mytable
# ⬆ Execution result (example)
                Table "public.mytable"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(10) |           |          | 
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

Table CRUD (Create, Read, Update, Delete)

These are standard SQL statements. Just for reference.

# Create table (Table name, column name are just examples.)
CREATE TABLE mytable (
  id integer PRIMARY KEY, 
  name varchar(10)
);

# Delete table
DROP table mytable;

# Read all data
SELECT * FROM mytable;

# Insert
INSERT INTO mytable VALUES (2, 'Nakagawa');

# Update
UPDATE mytable SET name = 'Asai' where id = 2;

# Delete
DELETE FROM mytable; # ←の場合、全データが削除されます

Operation of roles (Who has what kind of authority and can access which table)

The following commands are typed after entering the SQL input reception state.

Command to check the list of roles　

\du;

# ⬆　Execution result
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 taro        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 jiro        |                                                            | {}
 saburo      | Superuser                                                  | {}

Command to check each role’s permissions

The following way, permissions can be assigned for each table.

\dp;

# ⬆　Execution result
                                   Access privileges
 Schema |  Name   | Type  |      Access privileges      | Column privileges | Policies 
--------+---------+-------+-----------------------------+-------------------+----------
 public | mytable | table | taro=arwdDxt/taro+          |                   | 
        |         |       | jiro=arwdDxt/taro           |                   | 

Access privileges の記号
a: INSERT(add)
r: SELECT(read)
w: UPDATE(write)
d: DELETE
D: TRUNCATE
x: REFERENCES

Command to create a new role

# In the case of a general user
CREATE ROLE username LOGIN PASSWORD 'password';
# ⬆ Please insert username:username, password:password.
# ⬆ If you don't put LOGIN, you won't be able to log in.

# It's okay without login permission (you can set it later)
CREATE ROLE testuser PASSWORD 'password';

# It's okay without password setting (you can set it later)
CREATE ROLE testuser;

# In the case of a superuser
CREATE ROLE username superuser;
# ⬆ Since LOGIN is not attached, you cannot log in even though you are a superuser...

Command to give the created role a password and LOGIN permission

#　 Set the password
ALTER ROLE username PASSWORD 'password';

# Grant login permission
ALTER ROLE username LOGIN;

Command to grant table operation permissions to the role

# If you want to give individual permissions
GRANT permissions ON tablename TO username;
# ⬆　Please input tablename:tablename, username:username.
# ⬆　Permissions can be selected from
SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、
CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE  から選択

# If you want to give all permissions
GRANT all ON tablename TO username;

Command to revoke table operation permissions from a role

REVOKE permission ON tablename FROM username;

Command to delete a role

DROP ROLE username;

How to operate the visually operable “pgAdmin”

DB Connection Setting

When you start it, the following screen will appear.

Right-click on [Servers], click [Create]→[Server…].

The following dialog will appear.
The Name is optional.　

Next, open the Connection tab.

When connected, the tree will look like the following. (The list of DBs will be displayed.)

How to view and change table data

There is a list of tables under DB→Schemas→public→Tables. (The following diagram is an example.)

If you want to see the table data, right-click on the table→view/Edit Data→Select All Rows.

The following screen will appear.

From here, you can change or insert values.

Finally

After this, you’re probably okay if you have ORM.