PostgreSQL 101: From what’s database to basic SQL queries

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?
• Work with PostgreSQL
  • 1. Install PostgreSQL
  • 2. Create Database
  • 3. Create tables
  • 4. Insert into: How to insert values
  • 5. Select: How to get table values
  • 6. Alter: How to add another column to an existing table
  • 7. Update: How to update existing row values
  • 8. Join: How to connect tables
  • 9. Delete: How to delete rows
  • 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?).

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?).

SQL allows us to communicate with relational databases.

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

Work with PostgreSQL

1. Install PostgreSQL

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

Preparation

1. (Optional) Download GUI for PostgreSQL: I chose pgAdmin 4 simply because it’s free (What are the 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”

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

1. (Optional for GUI) click connection tab

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

9 (Optional for GUI) You will see Explorer

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

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)

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
SQL ORDER BY Keyword

Also we have some SQL functions:
SQL COUNT(), AVG() and SUM() Functions

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