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 - RENAME COLUMN

In PostgreSQL, if you need to rename a column in a table, you can use the ALTER TABLE statement in combination with the RENAME COLUMN clause.

Syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Where:

• table_name: The name of the table where the column you want to rename is located.
• old_column_name: The current name of the column.
• new_column_name: The new name you want to assign to the column.

Example:

Imagine you have a table named employees and you want to rename a column from emp_name to employee_name. Here's how you would do it:

ALTER TABLE employees RENAME COLUMN emp_name TO employee_name;

Important Points to Remember:

1. Permissions: Only the owner of the table, or a superuser, can change the name of a column.

2. Referencing Columns: Any stored procedures, functions, views, or other database objects that reference the old column name will need to be updated to use the new column name after the renaming.

3. Triggers and Rules: If there are any triggers or rules associated with the table, ensure they aren't specifically referencing the old column name.

4. Application Code: If the table and column are being used by an application, you will need to update the application code to reference the new column name.

5. Consistency: It's essential to ensure database consistency. After renaming, thoroughly test your application to ensure that all operations are working as expected.

In conclusion, renaming a column in PostgreSQL is straightforward using the ALTER TABLE command. However, take precautions to ensure all dependent objects, code, and applications are updated to reference the new column name to maintain smooth operations.

1. Renaming a column in PostgreSQL table:

   ALTER TABLE your_table
   RENAME COLUMN old_column_name TO new_column_name;
   

2. Changing column name with ALTER TABLE RENAME COLUMN in PostgreSQL:

   ALTER TABLE your_table
   RENAME COLUMN old_column_name TO new_column_name;
   

3. Renaming multiple columns in PostgreSQL:

   ALTER TABLE your_table
   RENAME COLUMN old_column1 TO new_column1,
                 old_column2 TO new_column2;
   

4. Handling dependencies when renaming a column in PostgreSQL:

   -- First, drop dependent objects
   DROP INDEX IF EXISTS your_index;
   -- Rename column
   ALTER TABLE your_table
   RENAME COLUMN old_column_name TO new_column_name;
   -- Recreate dependent objects
   CREATE INDEX your_index ON your_table(new_column_name);
   

5. Renaming a column with data type change in PostgreSQL:

   ALTER TABLE your_table
   ALTER COLUMN old_column_name TYPE new_data_type;
   

6. Renaming columns in specific schema in PostgreSQL:

   ALTER TABLE schema_name.your_table
   RENAME COLUMN old_column_name TO new_column_name;
   

7. Using RENAME COLUMN with CASCADE in PostgreSQL:

   ALTER TABLE your_table
   RENAME COLUMN old_column_name TO new_column_name CASCADE;
   

8. Undoing a column rename with RENAME COLUMN in PostgreSQL:

   ALTER TABLE your_table
   RENAME COLUMN new_column_name TO old_column_name;
   

9. Renaming columns in temporary tables in PostgreSQL:

   ALTER TEMPORARY TABLE your_temp_table
   RENAME COLUMN old_column_name TO new_column_name;
   

10. Renaming columns in views in PostgreSQL:

    CREATE OR REPLACE VIEW your_view AS
    SELECT old_column_name AS new_column_name
    FROM your_table;
    

11. Renaming columns and foreign key relationships in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER TABLE your_foreign_table
    RENAME COLUMN old_column_name REFERENCES your_table(new_column_name);
    

12. Renaming columns with indexes in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER INDEX your_index_name
    RENAME TO your_new_index_name;
    

13. Renaming columns and constraints in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER TABLE your_table
    RENAME CONSTRAINT your_constraint_name TO your_new_constraint_name;
    

14. Renaming columns and sequences in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER SEQUENCE your_sequence
    RENAME TO your_new_sequence;
    

15. Renaming columns with CHECK constraints in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER TABLE your_table
    RENAME CONSTRAINT your_check_constraint TO your_new_check_constraint;
    

16. Renaming columns and triggers in PostgreSQL:

    ALTER TABLE your_table
    RENAME COLUMN old_column_name TO new_column_name;
    
    ALTER TRIGGER your_trigger
    RENAME TO your_new_trigger;