How to use PostgreSQL on Mac

PostgreSQL(psql, PG) is an open source object-relational database system. In this article, we will talk about how to get start with PostgreSQL on Mac.

Install PostgreSQL using Homebrew

In your terminal, brew install postgresql command can help you automatically download and install PostgreSQL. After installation, you can open a new terminal to use PostgreSQL.

Start and stop database services

Before using PostgreSQL, you should use brew services start postgresql to start database services. After using PostgreSQL, you should use brew services stop postgresql to stop database services.

Start using PostgreSQL

You can use psql postgres to start the PostgreSQL teminal:

Then you need to create a new user using the following command:

-- 1. Create a new user:
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- 2. Give the new user privileges to create, edit, and delete databases:
ALTER ROLE username CREATEDB;

-- If necessary, you can delete role using this code:
DROP ROLE role_name;

After creating a new user, you should quit the PostgreSQL terminal using \q, then restart it using psql postgres -U username:

Note that the command prompt changes from postgres=#(which means you are are a superuser, because you installed the PostgreSQL instance yourself) to postgres=> .

Create and access database

To create a new database, you should use the CREATE DATABASE database_name command:

And then you can use \c database_name to connect to the database you created:

Common commands in PostgreSQL

To get familiar with PostgreSQL commands, inside the PostgreSQL terminal you can type \? (for internal commands) or \help (for SQL commands) to obtain the help documentation. Here are some common commands:

Command	Description
\l	list databases
\dt	list tables
\d table_name	describe table
\i file_name	execute commands from file, e.g., \i filepath/filename.sql
\e	edit the query buffer (or file) with external editor

\e command

Now I want to talk about \e command in detail. Sometimes if you need to input many SQL statements, it will be inconvenient to write them line by line directly in the PostgreSQL terminal. In this case, \e command can help you launch into a default text editor. This editor uses VIM environment, in which you can write SQL scripts. And when you write and quit(i.e., using :wq in VIM), PostgreSQL can automatically run the SQL commands saved in the editor and return the results.

In addition, in one terminal session, the SQL statements executed previously are all saved in this editor, so you can modify them if necessary.