PostgreSQL Keywords

Keyword Categories

SELECT, FROM, WHERE,
ORDER BY, GROUP BY

INSERT, UPDATE, DELETE,
MERGE

CREATE, ALTER, DROP,
TRUNCATE

GRANT, REVOKE,
ROLE, USER

🔹 SELECT Keywords

SELECT keywords are used to query and retrieve data from tables. These are the most commonly used keywords in PostgreSQL for data retrieval operations.

-- Basic SELECT
SELECT * FROM products;

-- SELECT with specific columns
SELECT name, price FROM products;

-- SELECT with WHERE clause
SELECT name, price 
FROM products
WHERE price > 100;

-- SELECT with ORDER BY
SELECT name, price
FROM products
ORDER BY price DESC;

-- SELECT with LIMIT
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- SELECT with DISTINCT
SELECT DISTINCT category
FROM products;

name    | price
--------+--------
Laptop  | 999.99
Monitor | 299.99

🔹 JOIN Keywords

JOIN keywords combine rows from multiple tables based on related columns. They are essential for working with relational data across different tables.

-- INNER JOIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN
SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN
SELECT p.name, c.color
FROM products p
CROSS JOIN colors c;

name     | order_date
---------+------------
John Doe | 2024-10-14
Jane Doe | 2024-10-13

🔹 Aggregate Keywords

Aggregate keywords perform calculations on multiple rows and return a single result. Use them with GROUP BY to analyze data by categories.

-- COUNT
SELECT COUNT(*) as total_users
FROM users;

-- SUM
SELECT SUM(price) as total_revenue
FROM orders;

-- AVG
SELECT AVG(price) as average_price
FROM products;

-- MAX and MIN
SELECT MAX(price) as highest, MIN(price) as lowest
FROM products;

-- GROUP BY with aggregate
SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category;

-- HAVING clause
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

category    | product_count
------------+--------------
Electronics |            15
Clothing    |            23

🔹 INSERT, UPDATE, DELETE Keywords

Data manipulation keywords modify table contents. INSERT adds new rows, UPDATE changes existing data, and DELETE removes rows from tables.

-- INSERT single row
INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

-- INSERT multiple rows
INSERT INTO products (name, price, stock)
VALUES 
    ('Laptop', 999.99, 10),
    ('Mouse', 25.99, 50),
    ('Keyboard', 79.99, 30);

-- UPDATE data
UPDATE products
SET price = 899.99
WHERE name = 'Laptop';

-- UPDATE with multiple columns
UPDATE users
SET email = '[email protected]',
    updated_at = NOW()
WHERE id = 1;

-- DELETE data
DELETE FROM products
WHERE stock = 0;

-- DELETE with condition
DELETE FROM users
WHERE created_at < '2020-01-01';

INSERT 0 3
UPDATE 1
DELETE 2

🔹 CREATE, ALTER, DROP Keywords

Data definition keywords manage database structure. CREATE builds new objects, ALTER modifies existing ones, and DROP removes them permanently.

-- CREATE TABLE
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2)
);

-- CREATE INDEX
CREATE INDEX idx_email ON users(email);

-- ALTER TABLE - Add column
ALTER TABLE employees
ADD COLUMN department VARCHAR(50);

-- ALTER TABLE - Modify column
ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- ALTER TABLE - Drop column
ALTER TABLE employees
DROP COLUMN department;

-- DROP TABLE
DROP TABLE IF EXISTS temp_data;

-- DROP INDEX
DROP INDEX IF EXISTS idx_email;

CREATE TABLE
CREATE INDEX
ALTER TABLE
DROP TABLE

🔹 Conditional Keywords

Conditional keywords filter and control query logic based on conditions:

-- WHERE with AND, OR
SELECT * FROM products
WHERE price > 100 AND stock > 0;

-- IN keyword
SELECT * FROM users
WHERE country IN ('USA', 'Canada', 'UK');

-- BETWEEN keyword
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE keyword
SELECT * FROM products
WHERE name LIKE '%phone%';

-- IS NULL / IS NOT NULL
SELECT * FROM users
WHERE email IS NOT NULL;

-- CASE keyword
SELECT name,
    CASE 
        WHEN price < 50 THEN 'Cheap'
        WHEN price < 200 THEN 'Moderate'
        ELSE 'Expensive'
    END as price_category
FROM products;

🔹 Reserved vs Non-Reserved Keywords

Understanding keyword types helps avoid naming conflicts:

-- Use double quotes to use keywords as names
CREATE TABLE "user" (
    "select" INTEGER,
    "from" VARCHAR(50)
);

-- Better practice: Avoid using keywords
CREATE TABLE app_user (
    user_select INTEGER,
    user_from VARCHAR(50)
);