How to get started with PostgreSQL
Tags: webdev, postgres
Published: 2022-04-26 12:31:33

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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/85udtwnnnqejot3pholq.gif)

## 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](https://www.postgresql.org/download/) 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](https://www.postgresql.org/docs/current/app-psql.html)

**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.