PostgreSQL Database

PostgreSQL을 이해 및 활용하기 위해
기본적으로 필요한 배경지식들을 정리하였습니다.

---

< Introduction to Database >

Databsae - Wikipedia

Database - Wikipedia

 

Article : What is a Database?

 

---

< What is CRUD? >

Create, Read, Update, and Delete (CRUD) are the four basic functions that a usable storage models should be able to do, at most.

 

Example : 

there would be a books resource, which would store book objects. Let’s say that the book object looks like this:

“book”: {
  "id": <Integer>,
  “title”: <String>,
  “author”: <String>,
  “isbn”: <Integer>
}

< Create >
This would consist of a function which we would call when a new library book is being added to the catalog. The program calling the function would supply the values for “title”, “author”, and “isbn”.
After this function is called, there should be a new entry in the books resource corresponding to this new book. Additionally, the new entry is assigned a unique id, which can be used to access this resource later.

< Read >
This would consist of a function which would be called to see all of the books currently in the catalog. This function call would not alter the books in the catalog - it would simply retrieve the resource and display the results.
We would also have a function to retrieve a single book, for which we could supply the title, author, or ISBN. Again, this book would not be modified, only retrieved.

< Update >
There should be a function to call when information about a book must be changed. The program calling the function would supply the new values for “title”, “author”, and “isbn”.
After the function call, the corresponding entry in the books resource would contain the new fields supplied.

< Delete >
There should be a function to call to remove a library book from the catalog. The program calling the function would supply one or more values (“title”, “author”, and/or “isbn”) to identify the book, and then this book would be removed from the books resource.
After this function is called, the books resource should contain all of the books it had before, except for the one just deleted.

 

+. CRUD and REST

: In a REST environment, CRUD often corresponds to the HTTP methods POST, GET, PUT, and DELETE, respectively. These are the fundamental elements of a persistent storage system.

 

 

---

< Introduction to PostgreSQL >

Video : What is PostgreSQL (a.k.a Postgres)

 

Article : SQLite vs MySQL vs PostgreSQL

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems | DigitalOcean

 

 

---

< Setting Up PostgreSQL >

Postgres itself is a database “server” There are several ways to connect to Postgres via “clients,” including 
GUIs, CLIs, and programming languages often via ORMs.
In order to run and use Postgres on your own computer, you will need to set up both a Postgres server and a client.

 

Server and Client

 

---

< Setting Up PostgreSQL (Server) >

• Windows : Postgres Download: Windows installers

PostgreSQL: Windows installers

 

• Mac OS X : Postgres Download: macOS packages
  recommend Postgres.app as it’s the easiest way to get started.

 

PostgreSQL: macOS packages

• Linux : downloading Postgres for Linux

PostgreSQL: Linux downloads (other)

 

---

< Setting Up PostgreSQL (Client) >

1. GUI : Postbird

• Windows & Max OS : Postbird website

Postbird | Apps | Electron

• Linux : 
  Install via Snap, which comes pre-installed on Ubuntu. From Terminal:
  

  sudo snap install postbird

+. In PostgreSQL, built-in GUI tool exists => pgAdmin4
+. I use 'TablePlus' GUI Tool

 

2. CLI : psql

< Windows >
The EDB installation of Postgres comes pre-packaged with command line tools. Simply open the Windows Command Prompt, and execute the .exe:

Microsoft Windows [Version 10.0.19041.264]
(c) 2020 Microsoft Corporation. All rights reserved.
 
C:\Users\avinashmoondra>"C:\Program Files\PostgreSQL\12\bin\psql.exe" --username postgres

You’ll be prompted for the password (postgres), and then you can run a test query! (assuming you’ve created a table during the GUI installation)

psql (12.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
 
postgres=# select * from films;
 id |    name    | release_date
----+------------+--------------
  1 | The Matrix | 1999-03-31
(1 row)

 

< Mac OS >
From Postgres.app, simply click on any database you’d like to connect to. It will automatically open up Terminal or iTerm (depending on your setting in Preferences) with psql CLI running.
From here, you can run a test query:

psql (12.3)
Type "help" for help.
 
postgres=# select * from films;
 id |    name    | release_date
----+------------+--------------
  1 | The Matrix | 1999-03-31
(1 row)​

 

< Linux >
postgresql-12 installation via apt-get comes with command-line tools.

parallels@parallels-Parallels-Virtual-Platform:~$ sudo -u postgres psql postgres
[sudo] password for parallels: 
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.
 
postgres=#

 

 

---

< PostgreSQL Basics >

Reference : 

PostgreSQL Getting Started (Official Postgres Tutorial)

Chapter 1. Getting Started

PostgreSQL Official Documentation

PostgreSQL 14.1 Documentation

 

< PostgreSQL Skill up site (exercise!) >

https://pgexercises.com/

PostgreSQL Exercises