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

PostgreSQL - ALTER TABLE

In PostgreSQL, the ALTER TABLE command is used to modify the structure of an existing table. This command allows for a wide variety of changes, such as adding or dropping columns, changing data types, renaming tables or columns, adding or removing constraints, and more.

Here's a comprehensive overview of the various uses of the ALTER TABLE command:

1. Rename a Table:

ALTER TABLE old_table_name RENAME TO new_table_name;

2. Add a New Column:

ALTER TABLE table_name ADD COLUMN column_name data_type [DEFAULT default_value] [constraints];

3. Rename a Column:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

4. Drop a Column:

ALTER TABLE table_name DROP COLUMN column_name [CASCADE | RESTRICT];

5. Change a Column's Data Type:

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type [USING expression];

6. Set a Default Value for a Column:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

7. Remove a Default Value from a Column:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

8. Add a Constraint:

For example, to add a UNIQUE constraint:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

9. Drop a Constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name [CASCADE | RESTRICT];

10. Add a Foreign Key Constraint:

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);

11. Set or Drop NOT NULL Constraint:

Set:

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

Drop:

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

12. Add or Drop a Primary Key:

Add:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Drop:

ALTER TABLE table_name DROP CONSTRAINT primary_key_name;

13. Change a Table's Tablespace:

ALTER TABLE table_name SET TABLESPACE tablespace_name;

Points to Remember:

• Altering tables, especially those with large amounts of data or those critical for application functionality, should be done with caution.

• Some alterations, like changing a column's data type, can be time-consuming and can lock the table, affecting application performance and accessibility.

• Always test changes in a development or staging environment before applying them to a production database.

• Backup your database before making structural changes. This allows for recovery in case something goes wrong.

• When dropping columns or constraints, be aware of the implications it might have on your applications, and ensure that no dependent code will break.

1. Adding a new column to a table in PostgreSQL:

   • Description: Expanding an existing table by introducing a new column.
   • Code Example:

     ALTER TABLE your_table
     ADD COLUMN new_column_name data_type;
     

2. Changing the data type of a column with ALTER TABLE in PostgreSQL:

   • Description: Modifying the data type of an existing column in a table.
   • Code Example:

     ALTER TABLE your_table
     ALTER COLUMN existing_column_name TYPE new_data_type;
     

3. Renaming a column in PostgreSQL:

   • Description: Changing the name of an existing column in a PostgreSQL table.
   • Code Example:

     ALTER TABLE your_table
     RENAME COLUMN old_column_name TO new_column_name;
     

4. Dropping a column with ALTER TABLE in PostgreSQL:

   • Description: Removing a column from an existing table in PostgreSQL.
   • Code Example:

     ALTER TABLE your_table
     DROP COLUMN column_to_remove;
     

5. Adding or removing constraints with ALTER TABLE:

   • Description: Applying or removing constraints (e.g., UNIQUE, CHECK) to/from a table.
   • Code Examples:

     -- Adding a UNIQUE constraint
     ALTER TABLE your_table
     ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2);
     
     -- Removing a CHECK constraint
     ALTER TABLE your_table
     DROP CONSTRAINT check_constraint_name;
     

6. Altering the default value of a column in PostgreSQL:

   • Description: Changing the default value assigned to a column.
   • Code Example:

     ALTER TABLE your_table
     ALTER COLUMN column_name SET DEFAULT new_default_value;
     

7. Modifying table attributes in PostgreSQL:

   • Description: Adjusting various attributes of a PostgreSQL table.
   • Code Example:

     ALTER TABLE your_table
     SET (attribute_name = attribute_value);
     

8. PostgreSQL ALTER TABLE vs ALTER COLUMN:

   • Description: Distinguishing between modifying a table and modifying a column.
   • Code Example:

     -- ALTER TABLE
     ALTER TABLE your_table SET (attribute_name = attribute_value);
     
     -- ALTER COLUMN
     ALTER TABLE your_table ALTER COLUMN column_name TYPE new_data_type;