Installation

There are several ways how to install Postgresql in Windows/Mac/Linux

Using Packages and Installers

You can find out this installation or package from official Postgresql website here.

Using Docker or Container

Postgresql have official Docker image hosted in Docker Hub.

PostgreSQL Native Data Types

from the most common:

1. Number
   • integer with range -/+2 bilion,
   • smallint
   • bigint
   • numeric or decimal (eg. 123.45 would require a numeric(5,2))
   • real and double precision is for floating values or data science application
2. Character
   • char(n) the characters should fit n number
   • varchar(n) with length max n
   • text is for blog post or article
3. Date/Time
   • date stores dates between 4713 BC and 5874897 AD
   • time will store time of day accurate to 1 microsecond
   • timestamp to record both time and date in one column
   • timestamp with time zone
4. Monetary
5. Binary
6. Boolean
7. Geometric etc…

PSQL Commands

psql is a terminal-based front-end to PostgreSQL.

Most used commands

Restore dump file

1

psql dbname < dumpfile

SQL Syntax

Create Database

First, check in into database

1

psql -d root

If you don’t have root database, you can use another database, psql -d another-db. Then you can create database with syntax

1

CREATE DATABASE new_db;

Alternatively, you can create database directly from terminal, without connect to any database, with command:

1

psql -U root "CREATE DATABASE newest_db"

But ensure you have already user and database called ‘root’. That’s why I mentioned it in this article, Docker Cheatsheet.

Create role with option

CREATE ROLE <role_name> [option]

list of option: https://www.postgresql.org/docs/current/sql-createrole.html

sample options:

• SUPERUSER
• LOGIN

For instance, you would like to create role called postgre

1

CREATE ROLE postgre SUPERUSER LOGIN;

Drop role

DROP ROLE <role_name>

References:

• Coding Notes by aysf