PostgreSQL - Syntax

PostgreSQL - Essential SQL Syntax

SQL (Structured Query Language) is the language used to interact with PostgreSQL. SQL commands can be grouped into several categories based on their function. This chapter provides a guide to the most common and essential commands, focusing on practical, modern usage. For a complete, exhaustive reference, please consult the official PostgreSQL documentation.

Understanding an SQL Statement

An SQL statement is composed of tokens, which are the basic building blocks of the language. These include keywords, identifiers, constants, and operators.

Token	SELECT	id, name	FROM	products	WHERE	price > 50.00	;
Type	Keyword	Identifiers	Keyword	Identifier	Keyword	Expression	Terminator

Data Definition Language (DDL)

DDL commands are used to define, modify, and remove database structures or objects.

CREATE TABLE

Defines a new table. This is one of the most fundamental commands.

CREATE TABLE users (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- Modern auto-incrementing key
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login TIMESTAMPTZ
);

ALTER TABLE

Modifies an existing table structure.

-- Add a new column
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- Change a column's data type
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Add a constraint
ALTER TABLE users ADD CONSTRAINT email_check CHECK (email LIKE '%@%.%');

-- Rename a column
ALTER TABLE users RENAME COLUMN last_login TO last_active_at;

DROP TABLE

Deletes a table and all of its data permanently.

-- Removes the 'users' table. This cannot be undone.
DROP TABLE users;

-- Use CASCADE to also drop dependent objects (e.g., views).
DROP TABLE users CASCADE;

Data Manipulation Language (DML)

DML commands are used to add, retrieve, modify, and delete data within tables.

INSERT INTO

Adds new rows of data to a table.

INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', 'hashed_password_string');

-- Insert multiple rows at once
INSERT INTO users (username, email, password_hash)
VALUES
    ('bob', 'bob@example.com', 'another_hash'),
    ('charlie', 'charlie@example.com', 'yet_another_hash');

SELECT

Retrieves data from one or more tables.

SELECT id, username, email, created_at
FROM users
WHERE is_active = true AND username LIKE 'a%'
ORDER BY created_at DESC
LIMIT 10;

UPDATE

Modifies existing data in a table.

-- Always use a WHERE clause to avoid updating all rows!
UPDATE users
SET last_active_at = NOW()
WHERE id = 1;

DELETE

Removes rows from a table.

-- Always use a WHERE clause to avoid deleting all rows!
DELETE FROM users
WHERE username = 'charlie';

Transaction Control Language (TCL)

TCL commands manage transactions, ensuring data integrity by grouping a set of operations into a single, all-or-nothing unit.

BEGIN; -- Start a transaction

-- Example: Transferring funds
UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

-- If both statements succeed, make the changes permanent
COMMIT;

-- If an error occurred, you would issue a ROLLBACK command instead
-- ROLLBACK;

Data Control Language (DCL)

DCL commands are used to manage user access and permissions to database objects.

GRANT

Gives specific permissions to a user or role.

-- First, create a role for your application
CREATE ROLE my_app_role;

-- Grant connect access to the database
GRANT CONNECT ON DATABASE my_app_db TO my_app_role;

-- Grant specific permissions on a table
GRANT SELECT, INSERT, UPDATE ON users TO my_app_role;

REVOKE

Removes permissions from a user or role.

-- Revoke the ability to update the users table
REVOKE UPDATE ON users FROM my_app_role;