Introduction to PostgreSQL and Relational Databases

Understand PostgreSQL, relational database concepts, SQL's role, and how data is organized.

What Is PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS) known for SQL standards support, transactional reliability, extensibility, and advanced data types. It stores structured data in tables made of rows and columns, while constraints and relationships help preserve correctness.

PostgreSQL supports transactional workloads, analytical queries, JSON data, full-text search, geospatial extensions such as PostGIS, and custom functions and types. It is not simply a place to store records: it provides mechanisms for consistency, concurrency, access control, recovery, and query optimization.

Note

PostgreSQL is commonly shortened to Postgres. SQL is the query language; PostgreSQL is the database system.

Basic SQL query

SELECT current_database(), current_user, version();

💡 This query reports the connected database, current database role, and PostgreSQL server version. It is a useful first check after connecting to a server.

Key Takeaways

PostgreSQL is an RDBMS with advanced SQL capabilities.
Tables represent structured data as rows and columns.
Constraints and transactions help protect data integrity.

Knowledge Check

What is PostgreSQL?

Tables, Rows, Columns, and Relationships

A table represents a kind of entity, such as customers or orders. Each row represents one record, and each column represents an attribute with a declared type. A primary key uniquely identifies a row. A foreign key links a value in one table to a key in another table, allowing the database to enforce referential integrity.

Relational design encourages storing each fact in an appropriate place and representing relationships explicitly. For example, an order should usually reference a customer rather than repeat all customer details. This reduces duplication and makes updates more reliable.

Customer and order relationship

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total NUMERIC(12, 2) NOT NULL CHECK (total >= 0)
);

💡 The order's customer_id references a real customer. The primary keys uniquely identify rows, while NOT NULL, UNIQUE, and CHECK constraints protect basic data rules.

Key Takeaways

A primary key identifies a row.
A foreign key enforces a relationship.
Use constraints to make important data rules enforceable by the database.

Knowledge Check

Which constraint enforces that an order references an existing customer?

SQL Command Families

SQL statements are often grouped conceptually into data definition, data manipulation, querying, and transaction control. CREATE, ALTER, and DROP define database objects; INSERT, UPDATE, and DELETE change rows; SELECT retrieves data; BEGIN, COMMIT, and ROLLBACK control transactions.

These categories help organize learning, but the most important skill is understanding what each statement does to data and schema. Be especially careful with destructive statements and run them against the intended database and environment.

Common SQL operations

-- Define structure
CREATE TABLE notes (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body TEXT NOT NULL);

-- Insert and read
INSERT INTO notes (body) VALUES ('Learn PostgreSQL');
SELECT id, body FROM notes;

-- Change a row
UPDATE notes SET body = 'Practice PostgreSQL' WHERE id = 1;

💡 The example creates a table, inserts a row, retrieves it, and updates it. In real applications, parameterized queries should be used to pass dynamic values safely.

Key Takeaways

DDL defines schema; DML changes rows; SELECT retrieves data.
Transaction statements control whether changes are committed.
Review WHERE clauses carefully before UPDATE or DELETE.

Knowledge Check

Which statement retrieves rows without changing table data?

What Is PostgreSQL?

Tables, Rows, Columns, and Relationships

SQL Command Families

STAY UPDATED WITH BYTES!