PostgreSQL 101: From what’s database to basic SQL queries
Tags: postgres
Published: 2023-07-12 05:38:28

In this article, I will share what I’ve learned about basic knowledge of database and queries with PostgreSQL examples including one of installation processes.

Contents:

* [What is database?](#what-is-database)
* [Work with PostgreSQL](#work-with-postgresql)
    * [1. Install PostgreSQL](#1-install-postgresql)
    * [2. Create Database](#2-create-database)
    * [3. Create tables](#3-create-tables)
    * [4. Insert into: How to insert values](#4-insert-into-how-to-insert-values)
    * [5. Select: How to get table values](#5-select-how-to-get-table-values)
    * [6. Alter: How to add another column to an existing table](#6-alter-how-to-add-another-column-to-an-existing-table)
    * [7. Update: How to update existing row values](#7-update-how-to-update-existing-row-values)
    * [8. Join: How to connect tables](#8-join-how-to-connect-tables)
    * [9. Delete: How to delete rows](#9-delete-how-to-delete-rows)
    * [10. Drop table: How to delete tables](#10-drop-table-how-to-delete-tables)


## What is database

A database is an organized collection of structured data stored electronically, typically managed by a DBMS, forming a database system ([What is database software?](https://www.oracle.com/ca-en/database/what-is-database/#:~:text=A%20database%20typically%20requires%20a,information%20is%20organized%20and%20optimized.)).

There are two main DBMS: Relational databases and NoSQL/Non relational database. I will focus more on relational databases in this article as PostgreSQL is a relational database.

**Relational database** is consist of tables with columns and rows. Columns hold specific data types and fields store attribute values. Rows represent a collection of related values. Rows can be so called a primary key, an unique identifier and relationships between rows in different tables are established using foreign keys ([What is a Relational Database?](https://aws.amazon.com/relational-database/)).

![](https://cdn-images-1.medium.com/max/3464/1*EiCqi4vxa_NzqT1qY9pXEQ.png)

SQL allows us to communicate with relational databases.

![](https://cdn-images-1.medium.com/max/3252/1*svIr5vBEsIZ3Z46mD2lCvg.png)

**NoSQL/Non relational databases **are databases which store data in a different way from relational databases. There are variety of ways to store data such as document, key-value, wide-column, and graph ([What is NoSQL?](https://www.mongodb.com/nosql-explained#:~:text=NoSQL%20databases%20(aka%20%22not%20only,wide%2Dcolumn%2C%20and%20graph.)).

## Work with PostgreSQL

### 1. Install PostgreSQL

Here is a way to download PostgreSQL on MacOS with homebrew.

**Preparation**

0. (Optional) Download GUI for PostgreSQL: I chose pgAdmin 4 simply because it’s free ([What are the best Mac OS X GUIs for PostgreSQL?](https://www.slant.co/topics/59/~best-mac-os-x-guis-for-postgresql))

 1. make sure homebrew is updated: brew update

 2. confirm that everything is working fine: brew doctor

**Installation**

 1. brew install postgresql 
Check the command to start postgresql service. It was brew services start postgresql@14 in my case.

### 2. Create Database

 1. Start the service: brew services start postgresql@14 
You will see a success message like Successfully started ‘postgresql@14’ (label: homebrew.mxcl.postgresql@14)

 2. Create a database: createdb ‘test’

 3. (Optional for GUI) create an user: createuser testuser

 4. (Optional for GUI) open pgAdmin4

 5. (Optional for GUI) Click “Add New Server”

![](https://cdn-images-1.medium.com/max/4060/1*oiRsbUL-ORJIBs97exZXVQ.png)

6. (Optional for GUI) Input the server name (e.g. localhost)

![](https://cdn-images-1.medium.com/max/4060/1*L7tEX2YJEqw8ADPnwCc_hQ.png)

7. (Optional for GUI) click connection tab

8. (Optional for GUI) Input database name, user name, and address

![](https://cdn-images-1.medium.com/max/4068/1*sp60e91d5mC9EYTHsaQRKQ.png)

9 (Optional for GUI) You will see Explorer

![](https://cdn-images-1.medium.com/max/4080/1*bJGDGH6SYhgTlWTq1MU2dw.png)

10. Connect to the database: psql 'test' (and \q to exit)

### 3. Create tables

Make sure you’ve connected the target database before creating tables.

Syntax:

    CREATE TABLE table_name (column_1 datatype, ..., column_n datatype);
    
    # Example
    CREATE TABLE users (name text, age smallint, birthday date);

You can find various data types provided by PostgreSQL here.
[**Chapter 8. Data Types**](https://www.postgresql.org/docs/current/datatype.html)

Then you will find the table with GUI, or you can check with \d command with Terminal.

    # Command result
     
    List of relations
     Schema | Name  | Type  |    Owner     
    --------+-------+-------+--------------
     public | users | table | ownername
    (1 row)

![tables in GUI](https://cdn-images-1.medium.com/max/4024/1*xMPA7rbLPzMDyz3wlJN37Q.png)

### 4. Insert into: How to insert values

Syntax:

    INSERT INTO table_name (column_1, ..., column_n) VALUES (value_1, ... value_n);
    
    # Example
    INSERT INTO users (name, age, birthday) VALUES ('Hermione', 10, '1979-09-19');

### 5. Select: How to get table values

Basic syntax:

    SELECT column_1, ..., column_n FROM table_name;
    
    # Example1
    SELECT name, age, birthday FROM users;
    
    # Result1
     name     | age |  birthday  
    ----------+-----+------------
     Hermione |  10 | 1979-09-19
    
    # Example2
    SELECT name FROM users;
    
    # Result2
       name   
    ----------
     Hermione

When you want to select all columns:

    SELECT * FROM table_name;
    
    # Example
    SELECT * FROM users;
    
    # Result
     name     | age |  birthday  
    ----------+-----+------------
     Hermione |  10 | 1979-09-19

You can find some conditional selections:
[**SQL WHERE Clause**](https://www.w3schools.com/sql/sql_where.asp)
[**SQL ORDER BY Keyword**](https://www.w3schools.com/sql/sql_orderby.asp)

Also we have some SQL functions:
[**SQL COUNT(), AVG() and SUM() Functions**](https://www.w3schools.com/sql/sql_count_avg_sum.asp)

### 6. Alter: How to add another column to an existing table

Syntax:

    ALTER TABLE table_name ADD column_name datatype;
    
    # Example
    ALTER TABLE users ADD house text;
    
    # Result
       name   | age |  birthday  | house 
    ----------+-----+------------+-------
     Hermione |  10 | 1997-09-19 | 
     Ron      |  10 | 1980-03-01 | 

### 7. Update: How to update existing row values

Syntax:

    UPDATE table_name SET column_name1 = column_value1 WHERE column_name2 = column_value2;
    
    #Example
    UPDATE users SET house = 'Gryffindor' WHERE name = 'Hermione';
    
    # Result
       name   | age |  birthday  |   house    
    ----------+-----+------------+------------
     Ron      |  10 | 1980-03-01 | 
     Hermione |  10 | 1997-09-19 | Gryffindor

### 8. Join: How to connect tables

Syntax:

    SELECT * FROM table1_name JOIN table2_name ON table1_name.primary_key = table1_name.foreign_key

Suppose we have tier table below and want to get users information with rank data:

    CREATE TABLE tier (id serial NOT NULL PRIMARY KEY, rank text, name text UNIQUE NOT NULL);
    
    SELECT * FROM tier
     id | rank |   name   
    ----+------+----------
      1 | s    | Hermione
      2 | s    | Ron
      3 | s    | Harry
      4 | a    | Draco

Then the SQL query will be:

    SELECT name, age, birthday, house, rank FROM users JOIN tier ON users.name = tier.name;
    
    # Result
       name   | age |  birthday  |   house    | id | rank |   name   
    ----------+-----+------------+------------+----+------+----------
     Hermione |  10 | 1997-09-19 | Gryffindor |  1 | s    | Hermione
     Ron      |  10 | 1980-03-01 | Gryffindor |  2 | s    | Ron
     Harry    |  10 | 1980-07-31 | Gryffindor |  3 | s    | Harry
     Draco    |  10 | 1980-06-05 | Slytherin  |  4 | a    | Draco

### 9. Delete : How to delete rows

Syntax:

    DELETE FROM table_name WHERE your conditoin;
    
    # Example
    DELETE FROM users WHERE name='Voldemort';

### 10. Drop table: How to delete tables

Syntax:

    DROP TABLE table_name;
    
    # Example
    DROP TABLE users;

That’s it! Thank you for reading :)
The original article is [here](https://medium.com/@lada496/postgresql-101-from-whats-database-to-basic-sql-queries-1aea5caed904)