PostgreSQL Tutorial
PostgreSQL is one of the most advanced general-purpose object-relational database management systems, and it is open-source. Being open-source software, its source code is available under PostgreSQL license, a liberal open source license. Anyone with the right skills is free to use, modify, and distribute PostgreSQL in any form.
Contemporary relational databases like MySQL, MariaDB, Apache Hive, etc all operate on the similar principle of establishing relationships among data, but PostgreSQL has additional features like table inheritance and function overloading as PostgreSQL supports the concept of Data objects.
Table of Content
Basics
- PostgreSQL – Introduction
- Install PostgreSQL on Windows
- Install PostgreSQL on Mac
- PostgreSQL – Loading a Database
- PostgreSQL – Create Database
- PostgreSQL – ALTER DATABASE
- PostgreSQL – DROP DATABASE
- PostgreSQL – Rename Database
- PostgreSQL – Copy Database
- PostgreSQL – Size of a Database
- PostgreSQL – Show Databases
Data Types
- PostgreSQL – Data Types
- PostgreSQL – Boolean Data Type
- PostgreSQL – CHAR Data Type
- PostgreSQL – VARCHAR Data Type
- PostgreSQL – TEXT Data Type
- PostgreSQL – NUMERIC Data Type
- PostgreSQL – SMALLINT Integer Data Type
- PostgreSQL – INTEGER Data Type
- PostgreSQL – BIGINT Integer Data Type
- PostgreSQL – Date Data Type
- PostgreSQL – Timestamp Data Type.
- PostgreSQL – UUID Data Type
- PostgreSQL – TIME Data Type
- PostgreSQL – Interval Data Type
- PostgreSQL – User-defined Data Type
- PostgreSQL – Array Data Type
- PostgreSQL – hstore Data Type
- PostgreSQL – JSON Data Type
Querying & Filtering Data
- PostgreSQL – SELECT
- PostgreSQL – SELECT DISTINCT clause
- PostgreSQL – ORDER BY clause
- PostgreSQL – WHERE clause
- PostgreSQL – LIMIT clause
- PostgreSQL – FETCH clause
- PostgreSQL – IN operator
- PostgreSQL – IS NULL operator
- PostgreSQL – LIKE operator
- PostgreSQL – NOT LIKE operator
- PostgreSQL – BETWEEN operator
- PostgreSQL – HAVING clause
- PostgreSQL – GROUP BY clause
Managing Tables
- PostgreSQL – CREATE TABLE
- PostgreSQL – SELECT INTO
- PostgreSQL – Create Auto-increment Column using SERIAL
- PostgreSQL – CREATE SEQUENCE
- PostgreSQL – Identity Column
- PostgreSQL – ALTER TABLE
- PostgreSQL – Rename Table
- PostgreSQL – ADD COLUMN
- PostgreSQL – DROP COLUMN
- PostgreSQL – Change Column Type
- PostgreSQL – RENAME COLUMN
- PostgreSQL – DROP TABLE
- PostgreSQL – TRUNCATE TABLE
- PostgreSQL – Temporary Table
- PostgreSQL – Temporary table name
- PostgreSQL – Removing Temporary Table
- PostgreSQL – Copy a Table
- PostgreSQL – Comparing Tables
- PostgreSQL – Describe Table
- PostgreSQL – Show Tables
- PostgreSQL – Import CSV File Into Table
Modifying Data
- PostgreSQL – INSERT
- PostgreSQL – Insert multiple rows
- PostgreSQL – UPDATE
- PostgreSQL – DELETE
- PostgreSQL – Upsert
Conditionals
Control Flow
- PostgreSQL – IF Statement
- PostgreSQL – CASE Statement
- PostgreSQL – Loop Statement
- PostgreSQL – While Loops
- PostgreSQL – For Loops
- PostgreSQL – Exit
- PostgreSQL – Continue
Transactions & Constraints
- PostgreSQL – Transactions
- PostgreSQL – COMMIT
- PostgreSQL – BEGIN
- PostgreSQL – ROLLBACK
- PostgreSQL – Primary Key
- PostgreSQL – Foreign Key
- PostgreSQL – CHECK Constraint
- PostgreSQL – UNIQUE Constraint
- PostgreSQL – NOT NULL Constraint
Working with JOINS & Schemas
- PostgreSQL – Joins
- PostgreSQL – LEFT JOIN
- PostgreSQL – INNER JOIN
- PostgreSQL – FULL OUTER JOIN
- PostgreSQL – SELF JOIN
- PostgreSQL – Schema
- PostgreSQL – CREATE SCHEMA
- PostgreSQL – DROP SCHEMA
- PostgreSQL – ALTER SCHEMA
Roles & Permissions
- PostgreSQL – CREATE ROLE
- PostgreSQL – ALTER ROLE
- PostgreSQL – DROP ROLE
- PostgreSQL – GRANT
- PostgreSQL – REVOKE
- PostgreSQL – Role Membership
Working with Sets
- PostgreSQL – UNION operator
- PostgreSQL – INTERSECT Operator
- PostgreSQL – EXCEPT Operator
- PostgreSQL – GROUPING SETS
- PostgreSQL – CUBE
- PostgreSQL – ROLLUP
Subquery & CTEs
- PostgreSQL – ANY Operator
- PostgreSQL – ALL Operator
- PostgreSQL – EXISTS Operator
- PostgreSQL – CTE
- PostgreSQL – Deleting Duplicate Rows using Subquery
User-defined Functions
- PostgreSQL – CREATE FUNCTION Statement
- PostgreSQL – Function parameter modes
- PostgreSQL – Function Overloading
- PostgreSQL – Function that returns table
- PostgreSQL – Drop Function
Important In-Built Functions
- PostgreSQL – AVG() Function
- PostgreSQL – COUNT() Function
- PostgreSQL – MAX() Function
- PostgreSQL – MIN() Function
- PostgreSQL – SUM() Function
- PostgreSQL – FIRST_VALUE Function
- PostgreSQL – LAST_VALUE Function
- PostgreSQL – NTH_VALUE Function
- PostgreSQL – ROW_NUMBER Function
- PostgreSQL – CURRENT_DATE Function
- PostgreSQL – CURRENT_TIME Function
- PostgreSQL – EXTRACT Function
- PostgreSQL- CONCAT Function
- PostgreSQL – FORMAT Function
- PostgreSQL – UPPER function
- PostgreSQL- LOWER function
- PostgreSQL – REGEXP_MATCHES Function
- PostgreSQL – REGEXP_REPLACE Function
- PostgreSQL – REPLACE Function
Visit PostgreSQL In-Built functions for more.
PostgreSQL Advance Concepts
PostgreSQL PL/pgSQL
Variables & Constants
- PostgreSQL – Variables
- PostgreSQL – Select Into
- PostgreSQL – Row type variables
- PostgreSQL – Record type variable
- PostgreSQL – Constants
Stored Procedures
- PostgreSQL – Introduction to Stored Procedures
- PostgreSQL – CREATE PROCEDURE
- PostgreSQL – Drop Procedure
Working with Triggers
- PostgreSQL – Trigger
- PostgreSQL – CREATE TRIGGER
- PostgreSQL – DROP TRIGGER
- PostgreSQL – ALTER TRIGGER
- PostgreSQL – Disabling a Trigger
- PostgreSQL – Enabling a Trigger
Working with Views & Indexes
- PostgreSQL – CREATE INDEX
- PostgreSQL – DROP INDEX
- PostgreSQL – List Indexes
- PostgreSQL – UNIQUE Index
- PostgreSQL – Partial Index
- PostgreSQL – Index On Expression
- PostgreSQL – REINDEX
- PostgreSQL – Multicolumn Indexes
Errors & Exception Handling
.png)