PostgreSQL Basics¶

1. What is PostgreSQL?¶

PostgreSQL is an open-source relational database management system (RDBMS).

Features¶

Open Source: Free to use
SQL Standards Compliance: Follows ANSI SQL standards well
Extensibility: Supports JSON, arrays, user-defined types
ACID Compliance: Guarantees transaction reliability
Concurrency Control: MVCC (Multi-Version Concurrency Control)

Why Use PostgreSQL?¶

┌─────────────────────────────────────────────────────────────┐
│                PostgreSQL Advantages                         │
├─────────────────────────────────────────────────────────────┤
│  • Excellent performance for complex queries                 │
│  • Can be used like NoSQL with JSON/JSONB types             │
│  • Built-in full-text search                                │
│  • Geographic data support (PostGIS)                         │
│  • Suitable for large-scale data processing                 │
└─────────────────────────────────────────────────────────────┘

2. Comparison with Other Databases¶

Feature	PostgreSQL	MySQL	SQLite
License	PostgreSQL License	GPL	Public Domain
JSON Support	JSONB (high performance)	JSON	JSON (limited)
Concurrency	MVCC	InnoDB MVCC	File locking
Scalability	Very high	High	Low
Use Case	Enterprise, analytics	Web applications	Embedded, testing

3. Installation Methods¶

Docker (Recommended)¶

The fastest way to get started.

# Run PostgreSQL 16 container
docker run --name postgres-study \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

# Check if running
docker ps

# Connect to psql inside container
docker exec -it postgres-study psql -U myuser -d mydb

macOS (Homebrew)¶

# Install PostgreSQL
brew install postgresql@16

# Start service
brew services start postgresql@16

# Connect to default database
psql postgres

Linux (Ubuntu/Debian)¶

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Check service status
sudo systemctl status postgresql

# Connect as postgres user
sudo -u postgres psql

Linux (CentOS/RHEL)¶

# Add PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL
sudo dnf install -y postgresql16-server

# Initialize database
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# Start service
sudo systemctl start postgresql-16
sudo systemctl enable postgresql-16

Windows¶

Download installer from official download page
Run installation wizard
Set password
Use default port 5432
Install pgAdmin together (GUI tool)

4. Verify Installation¶

# Check PostgreSQL version
psql --version
# or
postgres --version

Example output:

psql (PostgreSQL) 16.1

5. psql Client¶

psql is PostgreSQL's interactive terminal client.

Connection Methods¶

# Default connection (local, current user)
psql

# Connect to specific database
psql -d mydb

# Connect with specific user
psql -U username -d dbname

# Connect with host/port
psql -h localhost -p 5432 -U username -d dbname

# Connect to Docker container
docker exec -it postgres-study psql -U myuser -d mydb

Meta Commands (Backslash Commands)¶

Commands in psql that start with \.

Command	Description
`\l`	List databases
`\c dbname`	Connect to database
`\dt`	List tables in current DB
`\dt+`	List tables (detailed)
`\d tablename`	Describe table structure
`\d+ tablename`	Describe table (detailed)
`\du`	List users (roles)
`\dn`	List schemas
`\df`	List functions
`\di`	List indexes
`\x`	Toggle expanded output mode
`\timing`	Toggle query execution time display
`\i filename`	Execute SQL file
`\o filename`	Save output to file
`\q`	Quit psql
`\?`	Help for meta commands
`\h`	Help for SQL commands
`\h SELECT`	Help for SELECT syntax

Practice: Basic Commands¶

-- After connecting to psql

-- List databases
\l

-- Check current connection info
\conninfo

-- List tables (initially empty)
\dt

-- View help
\?

6. Execute First Query¶

Simple Calculation¶

-- Use like a calculator
SELECT 1 + 1;

Output:

 ?column?
----------
        2
(1 row)

Print String¶

SELECT 'Hello, PostgreSQL!';

Output:

      ?column?
--------------------
 Hello, PostgreSQL!
(1 row)

Check Current Time¶

SELECT NOW();

Output:

              now
-------------------------------
 2024-01-15 10:30:45.123456+09
(1 row)

Check Version¶

SELECT version();

7. Basic SQL Syntax¶

Case Sensitivity¶

SQL keywords: Case insensitive (SELECT = select)
Table/column names: Stored as lowercase by default
Strings: Use single quotes ('Hello')

-- These three queries are identical
SELECT * FROM users;
select * from users;
Select * From Users;

Comments¶

-- Single line comment

/* Multi-line
   comment */

SELECT 1; -- Inline comment

Statement Termination¶

End statements with semicolon (;)
In psql, can input multiple lines and execute with ;

SELECT
    id,
    name,
    email
FROM users
WHERE active = true;

8. Database Creation and Deletion¶

Create Database¶

-- Basic creation
CREATE DATABASE mydb;

-- With options
CREATE DATABASE mydb
    ENCODING 'UTF8'
    LC_COLLATE 'ko_KR.UTF-8'
    LC_CTYPE 'ko_KR.UTF-8';

Switch Database¶

-- psql meta command
\c mydb

Output:

You are now connected to database "mydb" as user "postgres".

Delete Database¶

DROP DATABASE mydb;

-- Delete only if exists
DROP DATABASE IF EXISTS mydb;

9. Practice Examples¶

Practice 1: Verify Environment¶

-- 1. Check PostgreSQL version
SELECT version();

-- 2. Check current user
SELECT current_user;

-- 3. Check current database
SELECT current_database();

-- 4. Check current time
SELECT NOW();

-- 5. Check server configuration
SHOW server_version;
SHOW data_directory;

Practice 2: Create First Database¶

-- 1. Create study database
CREATE DATABASE study_db;

-- 2. List databases
\l

-- 3. Switch to new database
\c study_db

-- 4. Check connection info
\conninfo

Practice 3: Create Simple Table¶

-- 1. Create table
CREATE TABLE hello (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Insert data
INSERT INTO hello (message) VALUES ('Hello, PostgreSQL!');
INSERT INTO hello (message) VALUES ('My first table!');

-- 3. Query data
SELECT * FROM hello;

-- 4. Check table structure
\d hello

Example output:

 id |      message       |         created_at
----+--------------------+----------------------------
  1 | Hello, PostgreSQL! | 2024-01-15 10:30:45.123456
  2 | My first table!    | 2024-01-15 10:30:50.654321
(2 rows)

10. Troubleshooting¶

Connection Errors¶

Error: psql: error: connection refused

# Check service status
sudo systemctl status postgresql

# Start service
sudo systemctl start postgresql

Error: FATAL: password authentication failed

# Need to check and modify pg_hba.conf
# Or use correct password

Error: FATAL: database "username" does not exist

# Connect specifying database
psql -d postgres

# Check container status
docker ps -a

# Check container logs
docker logs postgres-study

# Restart container
docker restart postgres-study

Next Steps¶

Learn about database and user management in detail in 02_Database_Management.md!

PostgreSQL Basics¶

1. What is PostgreSQL?¶

Features¶

Why Use PostgreSQL?¶

2. Comparison with Other Databases¶

3. Installation Methods¶

Docker (Recommended)¶

macOS (Homebrew)¶

Linux (Ubuntu/Debian)¶

Linux (CentOS/RHEL)¶

Windows¶

4. Verify Installation¶

5. psql Client¶

Connection Methods¶

Meta Commands (Backslash Commands)¶

Practice: Basic Commands¶

6. Execute First Query¶

Simple Calculation¶

Print String¶

Check Current Time¶

Check Version¶

7. Basic SQL Syntax¶

Case Sensitivity¶

Comments¶

Statement Termination¶

8. Database Creation and Deletion¶

Create Database¶

Switch Database¶

Delete Database¶

9. Practice Examples¶

Practice 1: Verify Environment¶

Practice 2: Create First Database¶

Practice 3: Create Simple Table¶

10. Troubleshooting¶

Connection Errors¶

Docker Related¶

Next Steps¶