How to get started with PostgreSQL

PostgreSQL is an open source Relational Database Management System (RDBMS). In this article, I’ll provide an introduction to getting started with PostgreSQL. The mother site for PostgreSQL is http://www.postgresql.org you can read more.

Installing PostgreSQL

You can run the command below on your terminal to quickly install PostgreSQL in Ubuntu.

Ubuntu

// Refresh the apt-get repository
$ sudo apt-get update
// Install PostgreSQL
$ sudo apt-get install postgresql postgresql-contrib

To verify you’ve got PostgreSQL installed, run the following command to check your PostgreSQL version:

postgres --version

Others can download the latest version of PostgreSQL here and follow the installation steps.

Getting Started

1. Login to the PostgreSQL Server

 $ sudo -u postgres psql
   -- Run command "psql" as UNIX USER "postgres".
   -- Enter the CURRENT SUPERUSER password for sudo.
psql (14.2 (Ubuntu 14.2-1.pgdg21.10+1))
Type "help" for help

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

-- Display version
postgres=# SELECT version();
                                                           version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg21.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit

press q to quit display

-- Quit
postgres=# \q

2. Create Database, Create Table, CURD (Create-Update-Read-Delete) Records

-- Login to server
$ sudo -u postgres psql
......

-- List all databases via \l (or \list), or \l+ for more details
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | python=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

-- Create a new database called testdb
postgres=# CREATE DATABASE testdb;
CREATE DATABASE

-- Connect to testdb database via \c (or \connect)
-- Take note of the change of database name in the command prompt.
postgres=# \c testdb 
You are now connected to database "testdb" as user "postgres".

-- Display all tables (aka relations) via \dt or \dt+ for more details
testdb=# \dt
Did not find any relations.


testdb=# CREATE TABLE IF NOT EXISTS accounts (
    user_id serial PRIMARY KEY, -- AUTO_INCREMENT integer, as primary key
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

-- Display all tables in the current database, via \dt or \dt+ for more details
testdb=# \dt+
                                     List of relations
 Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------+-------+----------+-------------+---------------+---------+-------------
 public | accounts | table | postgres | permanent   | heap          | 0 bytes | 


-- Quit
testdb=# \q

• You need to end your SQL command with a semi-colon (;) every-time or \g. If you forget to enter a semi-colon (;), the command-prompt changes to "dbname-#" to indicate continuation. You can enter the semi-colon on the new line.

More on commands

• \?: show all psql commands.
• \h sql-command: show syntax on SQL command.
• \c dbname [username]: Connect to database, with an optional username (or \connect).

• Display Commands: You can append + to show more details.

         - \l: List all database (or \list).
         - \d: Display all tables, indexes, views, and sequences.
         - \dt: Display all tables.
         - \dv: Display all views.
         - \di: Display all indexes.
         - \ds: Display all sequences.
         - \dS: Display all system tables.
         - \dT: Display all types.
         - \du: Display all users.
  

• See more commands

3. Commonly-used SQL Data Types
The commonly-used SQL data types in PostgreSQL are:

1. INT, SMALLINT: whole number.
2. DATE, TIME, TIMESTAMP: date and time.
3. NULL: Represent known value of 0 and empty string).
4. SERIAL: auto-increment integer (AUTO_INCREMENT in MySQL).
5. DOUBLE: single and double precision floating-point number.
6. NUMERIC(m,n): decimal number with m total digits and n decimal places (DECIMAL(m,n) in MySQL).
7. CHAR(n) and VARCHAR(n): fixed-length string of n characters and variable-length string of up to n characters. String are enclosed by single quotes, e.g., 'Fredson', 'Python, django'.
8. User-defined types: The ones that don't come defautly.