Home

PostgreSQL Full Tutorial Menu List

Introduction to PostgreSQL:

Title	Remark
What is PostgreSQL?	Introduction to PostgreSQL, its features, and advantages.
Installing PostgreSQL	A guide to installing PostgreSQL on various operating systems.
PostgreSQL Setup and Configuration	Basic setup, configuration, and getting started with PostgreSQL.

Basic SQL Operations:

Title	Remark
Connecting to PostgreSQL	How to connect to a PostgreSQL database using psql and other tools.
Database Operations	Learn how to create, drop, and manage PostgreSQL databases.
Creating Tables	Creating tables with columns, data types, and constraints.
Inserting Data	How to insert new records into a PostgreSQL table.
Selecting Data	Basics of selecting data with the SELECT statement, including filtering and sorting.
Updating Data	How to update existing records in a table using the UPDATE statement.
Deleting Data	How to delete data from a PostgreSQL table.
TRUNCATE	Reset auto-increment counter and delete all rows in a table.

Advanced Querying:

Title	Remark
WHERE Clause	Using WHERE to filter results based on conditions.
AND, OR, NOT Operators	Using logical operators to combine multiple conditions in WHERE.
GROUP BY Clause	Grouping rows to perform aggregate functions like COUNT, AVG, SUM.
HAVING Clause	Filtering aggregated results using HAVING.
ORDER BY Clause	Sorting query results in ascending or descending order.
DISTINCT	Removing duplicates in the result set using the DISTINCT keyword.
JOINS	Combining data from multiple tables using different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
Subqueries	Using subqueries within SELECT, INSERT, UPDATE, and DELETE.
IN, BETWEEN, LIKE	Using IN, BETWEEN, and LIKE operators for advanced filtering.
EXPLAIN	Using EXPLAIN to analyze and optimize query performance.

Data Integrity and Constraints:

Title	Remark
Primary Key	Defining a primary key to uniquely identify each record in a table.
Foreign Key	Establishing relationships between tables using foreign keys.
Unique Constraints	Enforcing uniqueness for specific columns in a table.
Check Constraints	Using check constraints to ensure data validity.
Not Null Constraint	Enforcing non-null values for certain columns.

Data Manipulation:

Title	Remark
UPDATE	How to modify records in a table with the UPDATE statement.
INSERT with SELECT	Inserting data using a SELECT statement for bulk insertions.
Upsert (INSERT ON CONFLICT)	Handling upsert operations using INSERT ON CONFLICT.
COPY	Importing and exporting data using the COPY command for bulk data management.
Transactional Operations	Managing transactions with BEGIN, COMMIT, and ROLLBACK.

PostgreSQL Advanced Features:

Title	Remark
Views	Creating and managing views for easier query abstraction.
Materialized Views	Using materialized views to store complex query results for better performance.
Stored Procedures	Creating reusable stored procedures to encapsulate business logic.
Functions	Writing custom functions in PostgreSQL for advanced data manipulation.
Triggers	Automatically executing SQL statements with triggers before or after changes to the table.
Indexes	Creating and optimizing indexes to speed up query execution.
Partitioning	Splitting large tables into partitions for better performance and scalability.

Advanced PostgreSQL Topics:

Title	Remark
JSON Support	Working with JSON data types for storing semi-structured data.
Foreign Data Wrappers (FDW)	Querying external data sources with Foreign Data Wrappers.
Security & Roles	Managing security, roles, and permissions in PostgreSQL.
Backups and Restores	How to backup and restore databases using pg_dump and pg_restore.
Replication	Setting up replication for high availability and load balancing.
Performance Tuning	Optimizing PostgreSQL performance through query optimization, indexing, and server configuration.

PostgreSQL Tools and Extensions:

Title	Remark
pgAdmin	Using pgAdmin for managing PostgreSQL databases through a graphical interface.
pgBackRest	Using pgBackRest for efficient backup and restore of PostgreSQL databases.
PostGIS	Adding geographic object support to PostgreSQL with PostGIS extension.
PostgreSQL Extensions	An overview of PostgreSQL extensions to extend the functionality of PostgreSQL.

---

Title	Remark
ALTER SEQUENCE	ALTER SEQUENCE <table name>_<column name>_seq RESTART WITH <reset id>;
Connecting to a Remote PostgreSQL
Database
Split part
String
TRUNCATE	Reset auto increment counter and delete all the data tables
UPDATE
SqlBuilder WHERE IN	builder.WHERE("location_id IN(SELECT location_id FROM TABLE)")
COPY	COPY CSV data into Database table