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 ROLE

In PostgreSQL, roles are a fundamental part of the database's access control and permission system. Roles can be thought of as either users or groups, depending on how they're used. The ALTER ROLE command is used to modify an existing role in various ways.

Here's a brief rundown of how you can use ALTER ROLE:

1. Rename a Role:

To rename an existing role:

ALTER ROLE old_role_name RENAME TO new_role_name;

2. Change Password of a Role:

To set or change the password for a role:

ALTER ROLE role_name WITH PASSWORD 'new_password';

3. Set Role Attributes:

There are several attributes you can set for a role:

• LOGIN/NOLOGIN: Allow or disallow the role to log in.
• SUPERUSER/NOSUPERUSER: Grant or revoke superuser status.
• CREATEDB/NOCREATEDB: Allow or disallow the role to create databases.
• CREATEROLE/NOCREATEROLE: Allow or disallow the role to create other roles.
• INHERIT/NOINHERIT: Allow or disallow the role to inherit permissions from roles they are a member of.

For example, to allow a role to log in and create databases:

ALTER ROLE role_name WITH LOGIN CREATEDB;

4. Set Role Validity:

You can set an expiration date for the role's password:

ALTER ROLE role_name WITH PASSWORD 'password' VALID UNTIL 'YYYY-MM-DD';

5. Set Connection Limit for a Role:

To restrict the number of concurrent connections for a role:

ALTER ROLE role_name WITH CONNECTION LIMIT 5;

6. Change Role Membership:

You can also add or remove a role from another role (essentially adding a user to a group or vice versa):

GRANT role_to_grant TO role_name;
REVOKE role_to_revoke FROM role_name;

(Note: GRANT and REVOKE are separate commands from ALTER ROLE, but they're related to role management.)

7. Set Role's Default Tablespace:

To set a default tablespace for a role:

ALTER ROLE role_name SET default_tablespace = tablespace_name;

Points to Remember:

• Role changes don't affect sessions that are already connected. For example, if you revoke a permission from a role, currently connected sessions with that role will retain the permission until they reconnect.

• Many of these changes can have significant security implications. It's crucial to understand the implications of each change and to use these commands with caution, especially in production environments.

• Always make sure to have backups and test any changes in a safe environment before applying them in production.

1. Changing password for a role in PostgreSQL:

   • Description: Updating the password for an existing PostgreSQL role.
   • Code Example:

     ALTER ROLE your_role WITH PASSWORD 'new_password';
     

2. Granting and revoking privileges with ALTER ROLE in PostgreSQL:

   • Description: Assigning or removing privileges from a PostgreSQL role.
   • Code Examples:

     -- Granting SELECT privilege on a table
     ALTER ROLE your_role GRANT SELECT ON your_table TO another_role;
     
     -- Revoking INSERT privilege on a table
     ALTER ROLE your_role REVOKE INSERT ON your_table FROM another_role;
     

3. Modifying role attributes in PostgreSQL:

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

     ALTER ROLE your_role SET attribute_name = attribute_value;
     

4. Adding and removing roles in PostgreSQL:

   • Description: Creating or deleting PostgreSQL roles.
   • Code Examples:

     -- Adding a new role
     CREATE ROLE new_role LOGIN PASSWORD 'password';
     
     -- Removing an existing role
     DROP ROLE existing_role;
     

5. Setting login roles and connection limits with ALTER ROLE:

   • Description: Defining login roles and connection limits for a PostgreSQL role.
   • Code Example:

     ALTER ROLE your_role LOGIN CONNECTION LIMIT 5;
     

6. PostgreSQL ALTER ROLE vs ALTER USER:

   • Description: Understanding the difference between ALTER ROLE and ALTER USER.
   • Code Example:

     -- ALTER ROLE
     ALTER ROLE your_role SET option_name = option_value;
     
     -- ALTER USER
     ALTER USER your_user SET option_name = option_value;
     

7. Altering superuser status with ALTER ROLE in PostgreSQL:

   • Description: Changing the superuser status of a PostgreSQL role.
   • Code Example:

     ALTER ROLE your_role WITH SUPERUSER;
     

8. Managing role memberships with ALTER ROLE:

   • Description: Adding or removing roles from the membership of another role.
   • Code Examples:

     -- Adding a role to the membership
     ALTER ROLE your_role ADD MEMBER another_role;
     
     -- Removing a role from the membership
     ALTER ROLE your_role DROP MEMBER another_role;