PostgreSQL Tutorial For Beginners

In today’s world, there are ample solutions for a database. The easiest one to install and use is PostgreSQL. This article walks you through installing PostgreSQL on a MacbookPro, creating a user, database and, granting privileges to the freshly created user. Later we will create a schema and add an example table to this schema.

The easiest way to install PostgreSQL is to download the DMG from postgres.org. Another way is to install is using homebrew. I use homebrew to manage most of my software. Just run the following command.

prompt>> brew install postgres
....
To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

Once brew installs Postgres, there are two ways to run the database. One can choose to run it as a service, and the other way is to run it using pg_ctl. I will leave it up to you to select your options.

Running Postgres

prompt>> pg_ctl -D /usr/local/var/postgres start

pg_ctl¹ is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (postgres), or displaying the status of a running server. Although the server can be started manually, pg_ctl encapsulates tasks such as redirecting log output and properly detaching from the terminal and process group. It also provides convenient options for controlled shutdown.

# status of database
prompt>> pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 70440)
/usr/local/Cellar/postgresql/12.2/bin/postgres "-D" "."
# another way to check status
prompt>> pg_isready
/tmp:5432 - accepting connections
# launching postgres
prompt>> psql

psql² is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

Create a User

To be create a user, run the following command.

prompt>> createuser joe
# List Users
prompt>> psql
psql$> \du

List users

Creating a Database

In the last snippet, we see an error. This is due to the fact that the default database has not been initiated. To create the databse, run the following command.

# Create a DB
prompt>> createdb blahdb

prompt>> psql
# List Database
blahdb$ > \l
# Alternate command
blahdb$ > \list

List of Databases

Setting up a password and granting privileges for user

# setting password for user
blahdb$ > alter user joe password ‘<password_here>’;
# granting privileges
blahdb$ > GRANT ALL PRIVILEGES ON DATABASE blahdb TO "joe";
# connect to db as user
blahdb$ > \c blahdb joe
# verify connection information
blahdb$ > \conninfo

Verify connection information

Creating a schema and Table

While creating schema, one must remember that the schema name must be distinct from the name of any existing schema in the current database.

# create schema
blahdb$ > create schema trial;
# list all schemas
blahdb$ > select schema_name from information_schema.schemata;
# alternate command
blahdb$ > \dn

List schemas on Database

Create a table

# create a table
blahdb$ > CREATE TABLE trial.COMPANY(
ID INT PRIMARY KEY     NOT NULL,
NAME           TEXT    NOT NULL,
AGE            INT     NOT NULL,
ADDRESS        CHAR(50),
SALARY         REAL);
CREATE TABLE
# list tables for schema
blahdb$ > \dt trial.*

List tables for Schema

#postgresql #database #postgres #macos