Truong Phung

Posted on Oct 29

💻 Common PostgreSQL Commands 🐘

#webdev #postgres #cli #beginners

Below is a comprehensive example that covers many of the common and advanced features of PostgreSQL (Quick Setup), including data types, indexing, transactions, full-text search, JSON, extensions, and more.

Step-by-Step Comprehensive Example in PostgreSQL

1. Create a Database and Connect

CREATE DATABASE mydb;
\c mydb;

2. Create Tables and Define Data Types

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    total NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SERIAL: Auto-increment integer.
VARCHAR and TEXT: String types.
NUMERIC: Exact numeric with a precision of 10 and scale of 2.
TIMESTAMP: Date and Time without timezone.
REFERENCES: Set Foreign Key with constraint ON DELETE CASCADE for deleting relevant orders when user got deleted

3. Insert Data with Transactions

BEGIN;

INSERT INTO users (username, email, password) 
VALUES ('johndoe', 'john@example.com', 'password123');

INSERT INTO orders (user_id, total, status) 
VALUES (1, 99.99, 'Pending');

COMMIT;

BEGIN and COMMIT: Start and commit a transaction.

4. Indexing

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Create indexes on frequently queried columns to speed up lookups.

5. Foreign Key and ON DELETE CASCADE

When a user is deleted, their orders will be automatically deleted due to the ON DELETE CASCADE.

DELETE FROM users WHERE user_id = 1;

6. Joins and Queries

-- Get all orders with user information
SELECT u.username, o.order_id, o.total, o.status, o.created_at
FROM orders o
JOIN users u ON o.user_id = u.user_id
ORDER BY o.created_at DESC;

Example of an inner join with ordering.

7. Full-Text Search

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    tsv_body tsvector
);

-- Populate the tsvector column for full-text search
UPDATE articles SET tsv_body = to_tsvector('english', body);

-- Query using full-text search
SELECT * FROM articles WHERE tsv_body @@ to_tsquery('english', 'PostgreSQL');

tsvector and tsquery: Used for full-text indexing and searching.

8. JSON and JSONB Columns

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    details JSONB
);

-- Insert JSON data
INSERT INTO products (name, details)
VALUES ('Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "cpu": "i7"}}'::jsonb);

-- Querying JSON data
SELECT name, details->>'brand' AS brand, details->'specs'->>'ram' AS ram
FROM products
WHERE details->>'brand' = 'Dell';

JSONB: More efficient than JSON for querying and indexing.

9. Views

CREATE VIEW user_order_summary AS
SELECT u.username, COUNT(o.order_id) AS total_orders, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

View: A virtual table based on the result of a query.

10. Window Functions

SELECT order_id, total, status, created_at,
       RANK() OVER (ORDER BY total DESC) AS rank
FROM orders;

RANK(): A window function that provides a rank to each row based on total value.

11. Common Table Expressions (CTE)

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT o.order_id, u.username, o.total
FROM recent_orders o
JOIN users u ON o.user_id = u.user_id;

CTE: Used for modularizing complex queries and making them more readable.

12. Upserts (INSERT ... ON CONFLICT)

INSERT INTO users (username, email, password) 
VALUES ('johndoe', 'john@example.com', 'password123')
ON CONFLICT (email) 
DO UPDATE SET last_login = CURRENT_TIMESTAMP;

ON CONFLICT: Upsert feature to handle insertions or updates based on uniqueness constraints.

13. Triggers

CREATE OR REPLACE FUNCTION update_last_login() RETURNS TRIGGER AS $$
BEGIN
   NEW.last_login = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER last_login_trigger
BEFORE UPDATE ON users
FOR EACH ROW
WHEN (OLD.last_login IS DISTINCT FROM NEW.last_login)
EXECUTE FUNCTION update_last_login();

Trigger: Automatically updates last_login whenever the users table is updated.

14. Partitioning

CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

PostgreSQL allows for table partitioning based on range or list values.

15. Foreign Data Wrappers (FDW)

CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'remote_db');
CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_server OPTIONS (user 'remote_user', password 'password');

IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO local_schema;

Allows PostgreSQL to query other databases as if they were local tables.

16. Extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "hstore";

UUID-OSSP: Generates UUIDs.
HSTORE: Provides key-value storage within PostgreSQL.

17. Explain and Analyze

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

EXPLAIN: Provides the query execution plan, which is useful for optimization.
ANALYZE: Executes the query and provides timing information.

18. Backup and Restore

Backup:
```
 pg_dump mydb > mydb_backup.sql
```
Restore:
```
 psql mydb < mydb_backup.sql
```

19. Advanced Security (Row-Level Security)

 ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy ON users
    FOR SELECT
    USING (username = current_user);

Row-Level Security (RLS): Restricts access to specific rows based on conditions.

Some other features

Example above already covers a wide range of common and advanced features. However, there are a few additional features and topics that might be worth mentioning, especially in specific use cases or for more specialized operations. Here's a list of those additional features with brief explanations:

1.Materialized Views (with Refresh)

While standard views query data dynamically, Materialized Views store the result of a query and need to be manually refreshed when the underlying data changes.

CREATE MATERIALIZED VIEW user_order_summary_materialized AS
SELECT u.username, COUNT(o.order_id) AS total_orders, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

-- Refresh the materialized view to update the data
REFRESH MATERIALIZED VIEW user_order_summary_materialized;

2. Recursive Queries (WITH RECURSIVE)

Used for hierarchical or tree-like structures (e.g., categories, organizational charts).

WITH RECURSIVE subordinates AS (
    SELECT employee_id, manager_id, name FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

3. Generated Columns

Automatically computed columns based on expressions or other column values.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    quantity INT,
    total_cost NUMERIC(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

4. Advanced Partitioning (List, Range, Hash)

Different types of partitioning can be applied based on range, list, or hash values, allowing for efficient scaling and querying of large datasets.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT,
    total NUMERIC(10, 2),
    order_date DATE
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

5. Array Data Types and Array Functions

PostgreSQL supports array types, which allow storage of multiple values in a single column.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    skills TEXT[]
);

-- Insert an array of skills
INSERT INTO employees (name, skills) VALUES ('Alice', ARRAY['PostgreSQL', 'Python', 'Docker']);

-- Query to search for employees with a specific skill
SELECT * FROM employees WHERE 'PostgreSQL' = ANY(skills);

6. Geospatial Data with PostGIS

For geospatial applications, PostgreSQL can be extended with PostGIS to handle geographic data types and functions.

CREATE EXTENSION postgis;

CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY(POINT)
);

-- Insert geographic point data (latitude, longitude)
INSERT INTO places (name, location) VALUES ('Location1', ST_GeographyFromText('POINT(30.5 50.5)'));

-- Find all places within 10 km radius
SELECT name FROM places WHERE ST_DWithin(location, ST_MakePoint(30.0, 50.0)::geography, 10000);

7. Backup and Restore with Point-in-Time Recovery (PITR)

For advanced recovery, you can use WAL archiving to enable point-in-time recovery (PITR).

# In postgresql.conf
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

8. Advisory Locks

PostgreSQL supports advisory locks to provide application-level locking mechanisms for concurrency control.

-- Acquire an advisory lock
SELECT pg_advisory_lock(12345);

-- Release the advisory lock
SELECT pg_advisory_unlock(12345);

9. Parallel Queries

PostgreSQL supports parallelism for certain types of queries to improve performance on multi-core systems.

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

10. pg_stat_statements (Performance Monitoring)

The pg_stat_statements extension tracks query performance and execution statistics, which is useful for identifying slow queries.

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

11. Foreign Keys with Deferrable Constraints

Deferrable foreign keys allow you to temporarily defer the enforcement of foreign key constraints until the end of a transaction.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) DEFERRABLE INITIALLY DEFERRED
);

12. Event Triggers

Event triggers can be used to fire on schema changes such as CREATE, ALTER, or DROP.

CREATE FUNCTION log_ddl_commands() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'DDL Command: %', tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER trigger_ddl_commands ON ddl_command_start
EXECUTE FUNCTION log_ddl_commands();

13. Temporal Data with Range Types

PostgreSQL allows for time ranges using the built-in range types, such as tstzrange, daterange, etc.

CREATE TABLE room_bookings (
    booking_id SERIAL PRIMARY KEY,
    room_number INT,
    booking_period tstzrange
);

-- Insert a new booking
INSERT INTO room_bookings (room_number, booking_period)
VALUES (101, tstzrange('2024-10-01', '2024-10-07'));

-- Query overlapping bookings
SELECT * FROM room_bookings
WHERE booking_period && tstzrange('2024-10-05', '2024-10-10');

14. Logical Replication

PostgreSQL provides logical replication to replicate data selectively between databases.

-- On the publisher
CREATE PUBLICATION mypub FOR TABLE users;

-- On the subscriber
CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost dbname=mydb' PUBLICATION mypub;

Conclusion

The PostgreSQL features presented here, along with the ones previously mentioned, create a comprehensive view of what PostgreSQL can do. This includes basic operations, advanced queries, performance tuning, scalability features (like partitioning and parallel queries), full-text search, geospatial data, JSON support, and much more.

PostgreSQL is highly extensible, and depending on your needs, you may want to explore even more specialized topics such as:

Graph Data (with the AGE extension),
Temporal Tables (using pg_temporal),
Advanced Auditing using tools like pgaudit,
TimescaleDB for time-series data,
pg_partman for automated partitioning.

These advanced features make PostgreSQL one of the most powerful and flexible relational databases available.

If you found this helpful, let me know by leaving a 👍 or a comment!, or if you think this post could help someone, feel free to share it! Thank you very much! 😃