How to Start Using psql in Terminal (for Beginners)

Intro

Hello guys! Today I try to write about how to start using psql in Terminal app, which I leant recently. It's very simple and short article, but I hope you enjoy.

🙇‍♂️ Sorry for Window lovers, but this article is for Mac user...🍎

First Setup

The popular way to use psql in Mac terminal app is to use Homebrew. If you don't know about Homebrew, check here for information first.

Once Homebrew is installed on your computer, run the following code from the Terminal.

brew install postgresql

Tip: Difference between psql and PostgreSQL

When I first learned about psql, it was not clear to me what the difference was between psql and PostgreSQL. Both psql and PostgreSQL are terms related to SQL databases, but have different meanings.

While PostgreSQL is an open source relational database management system (RDBMS), psql is a front-end interface for interactive manipulation of PostgreSQL (CUI tool). psql interactively passes user input to PostgreSQL and displays the results. psql is typically run on a terminal (command line prompt).

As another option to use PostgreSQL, there is a GUI tool called pgAdmin. You can also check it if you are interested in.

Login to PostgreSQL on psql (to who knows own account and password)

Now you are ready to use PostgreSQL and psql on your computer.

But first, one thing I'd like to mention - if you are new to postgresql or psql, you may need to do some user configuration / initial setup, but unfortunately, I lost how I did that (I'm sorry...).

So, firstly let me explain how to login to account to who already knows account and password. And after that I will explain how to create user and database from your superuser account, to who wants to start with new.

Here is the command to login:

sudo -u <username> psql

If you run this code on Terminal, then you'd be asked to enter the password you set. When your password get authenticated successfully, then you will see <username>=# on the Terminal and you are able to use the psql!

For example, if you run \du, then you will see the list of user accounts. \q to quit psql.

Tip: Superuser Account

To use PostgreSQL, you should have your own account. By default, there is a superuser account named "postgres", but it is recommended that you create a new account instead of using this account.

Create New User and Database

Let's talk about how to create a new User and Database. And make it available.

First of all, login as superuser by run this code:

sudo -u postgres psql

Then enter your password you set. If you do not know the password you specified when you installed PostgreSQL, or if you are not a user with sudo, you will need to use another method. In such cases, please refer to the official PostgreSQL documentation and support resources.

Let's talk about next process. Run the following command:

CREATE USER <username> WITH PASSWORD 'any passwords';

Now you created new User here! Then run this command to create new Database:

CREATE DATABASE <you-database-name>;

And grant user accounts access to your new database:

GRANT ALL PRIVILEGES ON DATABASE <you-database-name> TO <username>;

By following this procedure, you have created a new user account and database.

Now run \q to quit, let's login to check how it works.

Outro

You can try many more things with the database created this time, such as create/delete tables add something, etc. But that's all for now (this was a bit of a short article, so apologies if it wasn't enough). I may write a sequel someday 😅 Thank you for reading, happy coding everyone!