Common PostgreSQL Statements

Introduction

This article covers several common statements used in PostgreSQL, which are highly fundamental and frequently applied in almost all projects utilizing a database.

General Concepts

In SQL generally and PostgreSQL specifically, the terms Statement, Query and Clause are used very frequently, yet they remain technically distinct with clear boundaries and hierarchies.

A Statement is like a complete sentence in a text.
- It is a fully executable, independent unit within a database. It begins with an action keyword and typically ends with a semicolon ; in execution.
- Examples include INSERT INTO products (name) VALUES ('Product name'); or an entire SELECT... block.
A Query is a question, regarded as a special type of statement.
- It is a special case of a statement because it is only used to read data, not modify it.
- It mainly refers to the SELECT statement, such as SELECT email FROM customers WHERE id = 1;
A Clause is like a phrase or clause that constructs the sentence itself.
- It is a sub-component that builds up a Statement. It begins with a functional keyword and cannot stand independently.
- Examples include FROM customers, WHERE price > 100 and ORDER BY created_at DESC

Take the following statement for example: SELECT fullname, email FROM customers WHERE status = 'active';

The line above is a Statement and more specifically, since it retrieves data, it is also a Query.
It is composed of three Clauses:
- SELECT fullname, email (SELECT clause)
- FROM customers (FROM clause)
- WHERE status = 'active' (WHERE clause)
Thus, every Query is a Statement (a Statement is assembled from multiple Clauses).
However, not every Statement is a Query. For example, using DELETE or CREATE TABLE is a Statement but not a Query because they do not query to return table data.

Other Important Concepts

Expanding further, there are even smaller building blocks which include:

Expression

Any combination of values, operators and functions that Postgres can evaluate to return a single value. Example: price * 0.1, age + 5 and COALESCE(phone, 'No Phone')

Predicate

A special form of Expression that only returns a Boolean value (TRUE, FALSE or UNKNOWN). They usually appear after WHERE or HAVING clauses. Example: price > 500 or status IS NULL.

Identifier

The names of database objects defined by you, such as table names, column names, database names and index names.
If an identifier conflicts with a reserved keyword or contains spaces, you must enclose it in double quotes "".
Example: In SELECT "Firstname", email FROM customers, the terms Firstname, email and customers are Identifiers.

Literal / Constant

Specific, fixed data values that you pass directly into a statement. Example:

'active' (strings must be enclosed in single quotes '')
100 (number)
TRUE (boolean).

Operator

Symbols used to perform calculations or comparisons. Example: +, -, *, /, =, !=, LIKE, IN, OR

Detail

CREATE

CREATE DATABASE ecommerce_db;

CREATE TABLE IF NOT EXISTS customers (
    customer_id SERIAL PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INT NOT NULL DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(12, 2) DEFAULT 0,
    status VARCHAR(30) DEFAULT 'pending'
);

You can see that CREATE DATABASE and CREATE TABLE are straightforward. Postgres also supports CREATE TABLE IF NOT EXISTS which automatically ignores the statement if the table already exists.
On the other hand, the CREATE DATABASE statement cannot run inside a transaction block. Therefore, Postgres does not allow direct conditional check clauses like IF NOT EXISTS for a Database object to avoid system resource conflicts.
You can visualize the ERD (Entity Relationship Diagram) in this manner.

INSERT

INSERT INTO customers (fullname, email, phone) VALUES
('John Doe', 'johndoe@email.com', '0901234567'),
('Jane Smith', 'janesmith@email.com', '0912345678'),
('Robert Johnson', 'robertj@email.com', '0923456789');

INSERT INTO products (product_name, price, stock_quantity) VALUES
('iPhone 15 Pro Max', 30000000.00, 50),
('Samsung Galaxy S24 Ultra', 28000000.00, 40),
('MacBook Air M3', 26000000.00, 15),
('Logitech MX Master 3S Mouse', 2500000.00, 0);

INSERT INTO orders (customer_id, total_amount, status) VALUES
(1, 32500000.00, 'processing'),
(2, 28000000.00, 'shipped');

This is a simple INSERT statement inserting data into multiple rows simultaneously.

QUERY

SELECT
  o.order_id,
  c.fullname,
  c.email,
  o.total_amount,
  o.status,
  o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE total_amount > 2000
ORDER BY o.order_date DESC;

If we break down this Query, it consists of the following components:

Clause: SELECT o.order_id or WHERE total_amount > 2000
Predicate (returns True/False): total_amount > 2000
Identifiers: orders, customers
Literal: 2000
Operator for comparison: >

UPDATE

UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_name = 'iPhone 15 Pro Max';

UPDATE orders 
SET status = 'completed', customer_id = 3
WHERE customer_id = 1;

This is the UPDATE statement for one or more columns in a table based on a condition.

ALTER

ALTER TABLE orders ADD COLUMN shipping_address TEXT;
ALTER TABLE orders RENAME COLUMN shipping_address TO shipping_address_updated;
ALTER TABLE orders DROP COLUMN shipping_address_updated;

Use ALTER to modify an existing table structure, including basic functions such as ADD/RENAME/DROP COLUMN.

DELETE & TRUNCATE

DELETE FROM products WHERE product_name = 'product_name' AND stock_quantity = 0;
DELETE FROM orders

TRUNCATE TABLE orders;
TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

Both statements are used to delete data within a table, but they differ as follows:

DELETE

Can delete specific rows row-by-row, by scanning the table and marking matching rows for deletion.
Slower performance due to the MVCC mechanism, which requires adding a dead tuple flag and inserting a new row, while triggering WAL logging to support data restoration.
Does not automatically reset the value of auto-incrementing columns (the next ID continues to increment).
Triggers BEFORE DELETE and AFTER DELETE triggers.

TRUNCATE

Deletes all data by deallocating data pages of the table and cannot delete specific rows.
Slower scanning is avoided, resulting in faster performance because it only removes the entire HEAP pages without scanning individual rows.
Provides the option to reset or not reset the ID sequence as desired (RESTART/CONTINUE IDENTITY).
Does not fire row-level triggers (FOR EACH ROW), but only fires statement-level triggers (FOR EACH STATEMENT).

DROP

DROP TABLE IF EXISTS customerss;
DROP DATABASE IF EXISTS ecommerce_dbs;

Happy coding!

See more articles here.

Search This Blog

How To Dev