PostgreSQL Tutorial

• 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

• PostgreSQL - CASE
• PostgreSQL - COALESCE
• PostgreSQL - NULLIF() Function
• PostgreSQL - CAST

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

PostgreSQL PL/pgSQL

• PostgreSQL - Dollar-Quoted String Constants
• PostgreSQL - Block Structure

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

• PostgreSQL - Errors and Messages
• PostgreSQL - Exception Handling
• PostgreSQL - Assert

Install PostgreSQL on Windows

To install PostgreSQL on Windows, you typically use the Windows interactive installer by EnterpriseDB, which provides a graphical interface to help you set up and configure your PostgreSQL installation. Here's how you can use this method:

Steps to Install PostgreSQL on Windows:

• Download the Installer:

  • Go to the official PostgreSQL download page.
  • Click the "Download the installer" link under the Windows section.
  • Choose the desired version of PostgreSQL and download the appropriate installer for your version of Windows (32-bit or 64-bit).

• Run the Installer:

  • Once the download completes, run the installer.
  • You might be prompted by the User Account Control (UAC). Click 'Yes' to allow the installation process.

• Installation Options:

  • The installer will first set up the necessary components, then it will present you with several setup options. This includes installing the PostgreSQL Server, pgAdmin (a graphical tool to manage and develop with PostgreSQL databases), Stack Builder, and command line tools.
  • Keep the default components selected, unless you have specific needs.

• Select Installation Directory:

  • Choose where you want to install PostgreSQL or keep the default directory.

• Data Directory:

  • The installer will prompt you for a directory to store data. The default is typically fine, but ensure that this directory resides on a disk with adequate space.

• Password:

  • You will be prompted to set a password for the 'postgres' user. Make sure to remember or securely store this password, as you will need it to connect to your PostgreSQL server.

• Port:

  • The installer will suggest port 5432, the default port for PostgreSQL. Unless you have a reason to change it, keep the default.

• Locale:

  • Choose the default locale for the database or keep the default.

• Complete the Installation:

  • Review the settings and proceed. The installer will then install PostgreSQL and the selected components.
  • After the installation is complete, you'll be given an option to launch Stack Builder. This tool can be used to install additional components, like extensions or drivers. For most users, it's fine to skip this step initially.

• Verify Installation:

• After installation, you can verify that PostgreSQL is working by launching the pgAdmin tool from the Start Menu.
• Connect to the PostgreSQL server using the password you set up during the installation.

• Command Line Access:

• If you wish to use PostgreSQL from the command line, navigate to the bin directory of the PostgreSQL installation (default is C:\Program Files\PostgreSQL\<version>\bin) and run the psql.exe command.

Remember, always back up your data before making major changes or updates to your PostgreSQL installation.