Skip to main content
Mapagam

82 SECTIONS · 1059 TOPICS

Postgresql Roadmap

  1. Installing PostgreSQL

    1. 1. Installing on Linux
    2. 2. Installing on macOS
    3. 3. Installing on Windows
    4. 4. Verifying Installation
    5. 5. Understanding PostgreSQL Components
    6. 6. Installing Development Tools
    7. 7. Setting Up System User
    8. 8. Configuring System Service
    9. 9. Installing Contrib Modules
    10. 10. Upgrading PostgreSQL Versions

  2. Configuring PostgreSQL Server

    1. 1. Initializing Database Cluster
    2. 2. Starting Server
    3. 3. Stopping Server
    4. 4. Restarting Server
    5. 5. Reloading Configuration
    6. 6. Setting Environment Variables
    7. 7. Configuring postgresql.conf
    8. 8. Setting Memory Parameters
    9. 9. Setting Connection Limits
    10. 10. Configuring Logging
    11. 11. Setting WAL Parameters
    12. 12. Understanding Configuration File Hierarchy

  3. Configuring Authentication

    1. 1. Understanding pg_hba.conf
    2. 2. Configuring Local Connections
    3. 3. Configuring TCP/IP Connections
    4. 4. Using MD5 Authentication
    5. 5. Using SCRAM-SHA-256 Authentication
    6. 6. Using Peer Authentication
    7. 7. Using Certificate Authentication
    8. 8. Configuring LDAP Authentication
    9. 9. Configuring RADIUS Authentication
    10. 10. Setting Connection Host Restrictions
    11. 11. Reloading Authentication Config

  4. Connecting to PostgreSQL

    1. 1. Connecting with psql Client
    2. 2. Using Connection Strings
    3. 3. Setting Connection Parameters
    4. 4. Using Password File
    5. 5. Setting psql Environment Variables
    6. 6. Connecting to Remote Server
    7. 7. Using SSL Connections
    8. 8. Checking Connection Status
    9. 9. Switching Databases
    10. 10. Disconnecting from Server

  5. Working with Databases

    1. 1. Creating Database
    2. 2. Listing Databases
    3. 3. Connecting to Database
    4. 4. Setting Database Owner
    5. 5. Setting Database Encoding
    6. 6. Setting Database Collation
    7. 7. Renaming Database
    8. 8. Setting Database Parameters
    9. 9. Viewing Database Details
    10. 10. Checking Database Size
    11. 11. Dropping Database

  6. Working with Schemas

    1. 1. Creating Schema
    2. 2. Listing Schemas
    3. 3. Setting Schema Owner
    4. 4. Setting Search Path
    5. 5. Viewing Current Search Path
    6. 6. Creating Schema with Authorization
    7. 7. Using Qualified Names
    8. 8. Renaming Schema
    9. 9. Changing Schema Owner
    10. 10. Dropping Schema
    11. 11. Dropping Schema Cascade

  7. Creating and Managing Tables

    1. 1. Creating Table
    2. 2. Creating Table with Constraints
    3. 3. Creating Temporary Table
    4. 4. Creating Unlogged Table
    5. 5. Creating Table from Query
    6. 6. Creating Table Like Another
    7. 7. Listing Tables
    8. 8. Describing Table Structure
    9. 9. Renaming Table
    10. 10. Setting Table Owner
    11. 11. Copying Table Structure and Data
    12. 12. Truncating Table
    13. 13. Dropping Table

  8. Modifying Table Structure

    1. 1. Adding Column
    2. 2. Adding Multiple Columns
    3. 3. Dropping Column
    4. 4. Renaming Column
    5. 5. Changing Column Type
    6. 6. Setting Column Default
    7. 7. Dropping Column Default
    8. 8. Setting Column NOT NULL
    9. 9. Dropping NOT NULL Constraint
    10. 10. Setting Column Statistics Target
    11. 11. Setting Column Storage

  9. Working with Data Types

    1. 1. Using Integer Types
    2. 2. Using Numeric Types
    3. 3. Using Serial Types
    4. 4. Using Character Types
    5. 5. Using Boolean Type
    6. 6. Using Date and Time Types
    7. 7. Using INTERVAL Type
    8. 8. Using UUID Type
    9. 9. Using JSON Types
    10. 10. Using Array Types
    11. 11. Using ENUM Types
    12. 12. Using Composite Types
    13. 13. Using Domain Types
    14. 14. Using Range Types
    15. 15. Using Network Types
    16. 14. Using Range Types
    17. 15. Using Network Types

  10. Working with Constraints

    1. 1. Adding PRIMARY KEY Constraint
    2. 2. Adding UNIQUE Constraint
    3. 3. Adding NOT NULL Constraint
    4. 4. Adding CHECK Constraint
    5. 5. Adding DEFAULT Values
    6. 6. Adding FOREIGN KEY Constraint
    7. 7. Using ON DELETE Actions
    8. 8. Using ON UPDATE Actions
    9. 9. Creating Named Constraints
    10. 10. Adding Column Constraints
    11. 11. Adding Table Constraints
    12. 12. Deferring Constraints
    13. 13. Using Exclusion Constraints
    14. 14. Dropping Constraints
    15. 15. Disabling Constraints

  11. Inserting Data

    1. 1. Inserting Single Row
    2. 2. Inserting Multiple Rows
    3. 3. Inserting Specific Columns
    4. 4. Inserting from SELECT Statement
    5. 5. Using DEFAULT Keyword
    6. 6. Using DEFAULT VALUES Clause
    7. 7. Using RETURNING Clause
    8. 8. Using OVERRIDING SYSTEM VALUE
    9. 9. Using OVERRIDING USER VALUE
    10. 10. Inserting Array Values
    11. 11. Inserting JSON Values

  12. Updating Data

    1. 1. Updating Single Column
    2. 2. Updating Multiple Columns
    3. 3. Updating with WHERE Condition
    4. 4. Updating All Rows
    5. 5. Updating from Another Table
    6. 6. Updating with Subquery
    7. 7. Using RETURNING Clause
    8. 8. Updating JSON Fields
    9. 9. Updating Array Elements
    10. 10. Updating with Case Expression

  13. Deleting Data

    1. 1. Deleting Specific Rows
    2. 2. Deleting All Rows
    3. 3. Deleting with Subquery
    4. 4. Deleting from Multiple Related Tables
    5. 5. Using RETURNING Clause
    6. 6. Deleting with JOIN
    7. 7. Truncating Table
    8. 8. Truncating Multiple Tables
    9. 9. Truncating with CASCADE
    10. 10. Restarting Identity Sequences

  14. Using UPSERT Operations

    1. 1. Understanding INSERT ON CONFLICT
    2. 2. Using DO NOTHING
    3. 3. Using DO UPDATE SET
    4. 4. Specifying Conflict Target
    5. 5. Using WHERE Clause in DO UPDATE
    6. 6. Using excluded.*
    7. 7. Using RETURNING with UPSERT
    8. 8. Handling Unique Constraint Violations
    9. 9. Handling Primary Key Conflicts
    10. 10. Using UPSERT with Multiple Columns

  15. Querying Data with SELECT

    1. 1. Selecting All Columns
    2. 2. Selecting Specific Columns
    3. 3. Using Column Aliases
    4. 4. Using Expression Aliases
    5. 5. Selecting Distinct Values
    6. 6. Using DISTINCT ON
    7. 7. Limiting Results
    8. 8. Offsetting Results
    9. 9. Combining LIMIT and OFFSET
    10. 10. Using FETCH FIRST

  16. Filtering with WHERE Clause

    1. 1. Using Comparison Operators
    2. 2. Using Logical Operators
    3. 3. Using IN Operator
    4. 4. Using NOT IN Operator
    5. 5. Using BETWEEN Operator
    6. 6. Using NOT BETWEEN
    7. 7. Using LIKE Pattern Matching
    8. 8. Using ILIKE
    9. 9. Using NOT LIKE and NOT ILIKE
    10. 10. Using IS NULL
    11. 11. Using IS NOT NULL
    12. 12. Using IS DISTINCT FROM
    13. 13. Using IS NOT DISTINCT FROM

  17. Sorting Results

    1. 1. Sorting by Single Column
    2. 2. Sorting by Multiple Columns
    3. 3. Ascending Order
    4. 4. Descending Order
    5. 5. Sorting with NULL Values
    6. 6. Sorting by Expression
    7. 7. Sorting by Column Alias
    8. 8. Sorting by Column Position
    9. 9. Using COLLATE in ORDER BY
    10. 10. Combining ORDER BY with LIMIT

  18. Working with Joins

    1. 1. Using INNER JOIN
    2. 2. Using LEFT JOIN
    3. 3. Using RIGHT JOIN
    4. 4. Using FULL OUTER JOIN
    5. 5. Using CROSS JOIN
    6. 6. Using Self Join
    7. 7. Using NATURAL JOIN
    8. 8. Using JOIN with ON Clause
    9. 9. Using JOIN with USING Clause
    10. 10. Chaining Multiple Joins
    11. 11. Understanding Join Order and Performance
    12. 12. Using JOIN with WHERE Clause

  19. Grouping and Aggregating Data

    1. 1. Using GROUP BY Clause
    2. 2. Grouping by Multiple Columns
    3. 3. Using COUNT Function
    4. 4. Using SUM Function
    5. 5. Using AVG Function
    6. 6. Using MIN and MAX Functions
    7. 7. Using Aggregate Functions with DISTINCT
    8. 8. Filtering Groups
    9. 9. Using FILTER Clause
    10. 10. Using STRING_AGG
    11. 11. Using ARRAY_AGG
    12. 12. Using JSONB_AGG
    13. 13. Using BOOL_AND and BOOL_OR

  20. Using Advanced Grouping

    1. 1. Using GROUPING SETS
    2. 2. Using ROLLUP
    3. 3. Using CUBE
    4. 4. Using GROUPING Function
    5. 5. Combining GROUPING SETS with FILTER
    6. 6. Using Empty Grouping Set
    7. 7. Understanding GROUPING SETS vs UNION ALL
    8. 8. Optimizing Advanced Grouping Queries

  21. Working with Subqueries

    1. 1. Using Subquery in WHERE Clause
    2. 2. Using Subquery in FROM Clause
    3. 3. Using Subquery in SELECT Clause
    4. 4. Using Correlated Subqueries
    5. 5. Using EXISTS and NOT EXISTS
    6. 6. Using IN with Subquery
    7. 7. Using NOT IN with Subquery
    8. 8. Using ANY Operator
    9. 9. Using ALL Operator
    10. 10. Using SOME Operator
    11. 11. Using Lateral Joins
    12. 12. Understanding Subquery Performance

  22. Using Common Table Expressions

    1. 1. Creating Basic CTE
    2. 2. Using Multiple CTEs
    3. 3. Referencing CTEs in Queries
    4. 4. Creating Recursive CTE
    5. 5. Using Recursive CTE for Hierarchical Data
    6. 6. Using Recursive CTE for Graph Traversal
    7. 7. Using CTE with INSERT Statement
    8. 8. Using CTE with UPDATE Statement
    9. 9. Using CTE with DELETE Statement
    10. 10. Using MATERIALIZED Hint
    11. 11. Using NOT MATERIALIZED Hint
    12. 12. Understanding CTE vs Subquery Performance
    13. 13. Using SEARCH Clause in Recursive CTE
    14. 14. Using CYCLE Clause in Recursive CTE

  23. Working with Window Functions

    1. 1. Understanding Window Functions
    2. 2. Using ROW_NUMBER Function
    3. 3. Using RANK Function
    4. 4. Using DENSE_RANK Function
    5. 5. Using NTILE Function
    6. 6. Using LAG Function
    7. 7. Using LEAD Function
    8. 8. Using FIRST_VALUE Function
    9. 9. Using LAST_VALUE Function
    10. 10. Using NTH_VALUE Function
    11. 11. Using PARTITION BY Clause
    12. 12. Using ORDER BY in Window
    13. 13. Defining Window Frame
    14. 14. Using Named Windows
    15. 15. Using Aggregate Functions as Window Functions

  24. Using Window Frame Clauses

    1. 1. Understanding Frame Boundaries
    2. 2. Using UNBOUNDED PRECEDING
    3. 3. Using UNBOUNDED FOLLOWING
    4. 4. Using CURRENT ROW
    5. 5. Using n PRECEDING
    6. 6. Using n FOLLOWING
    7. 7. Using ROWS BETWEEN
    8. 8. Using RANGE BETWEEN
    9. 9. Using GROUPS BETWEEN
    10. 10. Using EXCLUDE Clause
    11. 11. Using CUME_DIST and PERCENT_RANK Functions

  25. Working with Conditional Expressions

    1. 1. Using CASE Expression
    2. 2. Using Simple CASE Expression
    3. 3. Using CASE in SELECT Clause
    4. 4. Using CASE in WHERE Clause
    5. 5. Using CASE in ORDER BY
    6. 6. Using COALESCE Function
    7. 7. Using NULLIF Function
    8. 8. Using GREATEST Function
    9. 9. Using LEAST Function
    10. 10. Using Boolean Expressions
    11. 11. Using IS TRUE, IS FALSE, IS UNKNOWN

  26. Working with String Functions

    1. 1. Concatenating Strings
    2. 2. Converting Case
    3. 3. Trimming Whitespace
    4. 4. Padding Strings
    5. 5. Finding Substrings
    6. 6. Extracting Substrings
    7. 7. Replacing Text
    8. 8. Splitting Strings
    9. 9. Reversing Strings
    10. 10. Repeating Strings
    11. 11. Calculating String Length
    12. 12. Using Format Function
    13. 13. Using Quote Functions

  27. Using Regular Expressions

    1. 1. Pattern Matching
    2. 2. Using REGEXP_MATCH Function
    3. 3. Using REGEXP_MATCHES Function
    4. 4. Using REGEXP_REPLACE Function
    5. 5. Using REGEXP_SPLIT_TO_TABLE Function
    6. 6. Using REGEXP_SPLIT_TO_ARRAY Function
    7. 7. Using Capture Groups
    8. 8. Using Flags
    9. 9. Using Character Classes
    10. 10. Using Anchors
    11. 11. Using REGEXP_COUNT Function
    12. 12. Using REGEXP_INSTR Function
    13. 13. Using REGEXP_SUBSTR Function
    14. 3. Using REGEXP_MATCHES Function
    15. 4. Using REGEXP_REPLACE Function
    16. 5. Using REGEXP_SPLIT_TO_TABLE Function
    17. 6. Using REGEXP_SPLIT_TO_ARRAY Function
    18. 7. Using Capture Groups
    19. 8. Using Flags
    20. 9. Using Character Classes
    21. 10. Using Anchors
    22. 11. Using REGEXP_COUNT Function
    23. 12. Using REGEXP_INSTR Function
    24. 13. Using REGEXP_SUBSTR Function
    25. 8. Using Flags
    26. 9. Using Character Classes
    27. 10. Using Anchors
    28. 11. Using REGEXP_COUNT Function
    29. 12. Using REGEXP_INSTR Function
    30. 13. Using REGEXP_SUBSTR Function
    31. 3. Using REGEXP_MATCHES Function
    32. 4. Using REGEXP_REPLACE Function
    33. 5. Using REGEXP_SPLIT_TO_TABLE Function
    34. 6. Using REGEXP_SPLIT_TO_ARRAY Function
    35. 7. Using Capture Groups
    36. 8. Using Flags
    37. 9. Using Character Classes
    38. 10. Using Anchors
    39. 11. Using REGEXP_COUNT Function
    40. 12. Using REGEXP_INSTR Function
    41. 13. Using REGEXP_SUBSTR Function
    42. 8. Using Flags
    43. 9. Using Character Classes
    44. 10. Using Anchors
    45. 11. Using REGEXP_COUNT Function
    46. 12. Using REGEXP_INSTR Function
    47. 13. Using REGEXP_SUBSTR Function
    48. 3. Using REGEXP_MATCHES Function
    49. 4. Using REGEXP_REPLACE Function
    50. 5. Using REGEXP_SPLIT_TO_TABLE Function
    51. 6. Using REGEXP_SPLIT_TO_ARRAY Function
    52. 7. Using Capture Groups
    53. 8. Using Flags
    54. 9. Using Character Classes
    55. 10. Using Anchors
    56. 11. Using REGEXP_COUNT Function
    57. 12. Using REGEXP_INSTR Function
    58. 13. Using REGEXP_SUBSTR Function

  28. Working with Mathematical Functions

    1. 1. Using Basic Arithmetic
    2. 2. Using Power and Root
    3. 3. Rounding Numbers
    4. 4. Using Absolute Value
    5. 5. Truncating Decimals
    6. 6. Using Sign Function
    7. 7. Generating Random Numbers
    8. 8. Using Modulo Operator
    9. 9. Using Exponential
    10. 10. Using Logarithms
    11. 11. Using Trigonometric Functions
    12. 12. Using PI Constant
    13. 13. Using Radians and Degrees
    14. 14. Using Factorial
    15. 15. Using GCD and LCM Functions

  29. Working with Date and Time

    1. 1. Getting Current Date and Time
    2. 2. Using LOCALTIMESTAMP and LOCALTIME
    3. 3. Formatting Dates
    4. 4. Parsing Date Strings
    5. 5. Extracting Date Parts
    6. 6. Truncating Dates
    7. 7. Using Date Arithmetic
    8. 8. Calculating Age Between Dates
    9. 9. Calculating Date Differences
    10. 10. Using Time Zones
    11. 11. Converting Between Time Zones
    12. 12. Using OVERLAPS Operator
    13. 13. Using ISFINITE Function
    14. 14. Creating INTERVAL Values
    15. 15. Using make_date and make_timestamp Functions

  30. Working with Arrays

    1. 1. Creating Array Columns
    2. 2. Inserting Array Data
    3. 3. Accessing Array Elements
    4. 4. Updating Array Elements
    5. 5. Querying Array Contents
    6. 6. Using Array Operators
    7. 7. Using Array Functions
    8. 8. Using array_append and array_prepend
    9. 9. Using array_cat
    10. 10. Using array_remove and array_replace
    11. 11. Unnesting Arrays
    12. 12. Using Array Aggregation
    13. 13. Using Array Slicing
    14. 14. Using Multidimensional Arrays
    15. 15. Creating GIN Index on Arrays

  31. Working with JSONB Data

    1. 1. Creating JSONB Columns
    2. 2. Inserting JSON Data
    3. 3. Using jsonb_build_object
    4. 4. Using jsonb_build_array
    5. 5. Accessing JSON Fields
    6. 6. Accessing JSON Text
    7. 7. Using Path Operators
    8. 8. Filtering with Containment
    9. 9. Using Existence Operators
    10. 10. Using jsonb_set
    11. 11. Using jsonb_insert
    12. 12. Removing JSON Keys
    13. 13. Using jsonb_strip_nulls
    14. 14. Converting JSON to Records
    15. 15. Using JSONB Aggregation

  32. Using SQL/JSON Path Queries

    1. 1. Understanding JSON Path Language
    2. 2. Using jsonb_path_query
    3. 3. Using jsonb_path_query_array
    4. 4. Using jsonb_path_query_first
    5. 5. Using jsonb_path_exists
    6. 6. Using jsonb_path_match
    7. 7. Using Path Accessors
    8. 8. Using Filter Expressions
    9. 9. Using Arithmetic in Paths
    10. 10. Using Comparison Operators in Paths
    11. 11. Using String Methods
    12. 12. Passing Variables to Path Queries

  33. Indexing JSONB Data

    1. 1. Creating GIN Index on JSONB
    2. 2. Using jsonb_ops Operator Class
    3. 3. Using jsonb_path_ops Operator Class
    4. 4. Indexing Specific JSON Paths
    5. 5. Using Partial Indexes on JSONB
    6. 6. Querying with Indexed JSON
    7. 7. Understanding GIN Index Performance
    8. 8. Using JSONB Indexes for Full-Text Search

  34. Working with Sequences

    1. 1. Creating Sequence
    2. 2. Using nextval Function
    3. 3. Using currval Function
    4. 4. Using lastval Function
    5. 5. Using setval Function
    6. 6. Creating Auto-Increment Columns
    7. 7. Using GENERATED ALWAYS AS IDENTITY
    8. 8. Using GENERATED BY DEFAULT AS IDENTITY
    9. 9. Altering Sequence
    10. 10. Resetting Sequence
    11. 11. Setting Sequence Ownership
    12. 12. Listing Sequences
    13. 13. Dropping Sequence

  35. Working with Generated Columns

    1. 1. Understanding Generated Columns
    2. 2. Creating Stored Generated Columns
    3. 3. Using Generated Columns with Expressions
    4. 4. Using Generated Columns with JSON
    5. 5. Creating Indexes on Generated Columns
    6. 6. Understanding Generated Column Limitations
    7. 7. Altering Generated Column Expression
    8. 8. Dropping Generated Column

  36. Working with Indexes

    1. 1. Creating Index
    2. 2. Creating Unique Index
    3. 3. Creating Partial Index
    4. 4. Creating Expression Index
    5. 5. Creating Multicolumn Index
    6. 6. Using B-tree Index
    7. 7. Using Hash Index
    8. 8. Using GiST Index
    9. 9. Using GIN Index
    10. 10. Using BRIN Index
    11. 11. Using SP-GiST Index
    12. 12. Creating Covering Index
    13. 13. Creating Index Concurrently
    14. 14. Listing Indexes
    15. 15. Dropping Index

  37. Managing Indexes

    1. 1. Reindexing Table
    2. 2. Reindexing Index
    3. 3. Reindexing Schema
    4. 4. Reindexing Database
    5. 5. Reindexing Concurrently
    6. 6. Checking Index Usage
    7. 7. Finding Unused Indexes
    8. 8. Finding Duplicate Indexes
    9. 9. Analyzing Index Bloat
    10. 10. Optimizing Index Maintenance

  38. Creating and Using Views

    1. 1. Creating View
    2. 2. Creating View with Column List
    3. 3. Querying Views
    4. 4. Creating Updatable Views
    5. 5. Using CHECK OPTION
    6. 6. Creating Recursive Views
    7. 7. Replacing View
    8. 8. Altering View
    9. 9. Renaming View
    10. 10. Listing Views
    11. 11. Dropping View
    12. 12. Using Security Barrier Views

  39. Creating and Using Materialized Views

    1. 1. Creating Materialized View
    2. 2. Creating with Data
    3. 3. Creating without Data
    4. 4. Querying Materialized Views
    5. 5. Refreshing Materialized View
    6. 6. Refreshing Concurrently
    7. 7. Creating Indexes on Materialized Views
    8. 8. Listing Materialized Views
    9. 9. Dropping Materialized View
    10. 10. Understanding Materialized View Use Cases

  40. Working with Functions

    1. 1. Creating Function
    2. 2. Using Function Parameters
    3. 3. Returning Single Value
    4. 4. Returning Table
    5. 5. Returning Set of Rows
    6. 6. Writing SQL Functions
    7. 7. Writing PL/pgSQL Functions
    8. 8. Declaring Variables
    9. 9. Assigning Values
    10. 10. Using IF Statements
    11. 11. Using CASE Statements
    12. 12. Using LOOP Statements
    13. 13. Using FOR Loops
    14. 14. Using WHILE Loops
    15. 15. Using FOREACH Loops

  41. Advanced Function Concepts

    1. 1. Handling Exceptions
    2. 2. Using RAISE Statement
    3. 3. Using ASSERT Statement
    4. 4. Using RETURN Statement
    5. 5. Using Function Overloading
    6. 6. Using DEFAULT Parameter Values
    7. 7. Using Named Parameters
    8. 8. Using STRICT Keyword
    9. 9. Using IMMUTABLE, STABLE, VOLATILE
    10. 10. Using SECURITY DEFINER vs SECURITY INVOKER
    11. 11. Using PARALLEL SAFE, PARALLEL UNSAFE, PARALLEL RESTRICTED
    12. 12. Listing Functions
    13. 13. Viewing Function Definition
    14. 14. Dropping Functions

  42. Working with Stored Procedures

    1. 1. Creating Procedure
    2. 2. Calling Procedure
    3. 3. Using Transaction Control
    4. 4. Using Parameters
    5. 5. Using Exceptions in Procedures
    6. 6. Replacing Procedure
    7. 7. Listing Procedures
    8. 8. Dropping Procedure
    9. 9. Understanding Functions vs Procedures

  43. Creating and Managing Triggers

    1. 1. Creating Trigger
    2. 2. Using BEFORE Triggers
    3. 3. Using AFTER Triggers
    4. 4. Using INSTEAD OF Triggers
    5. 5. Using Row-Level Triggers
    6. 6. Using Statement-Level Triggers
    7. 7. Creating Trigger Function
    8. 8. Using NEW Record
    9. 9. Using OLD Record
    10. 10. Using TG_OP Variable
    11. 11. Using TG_WHEN Variable
    12. 12. Using TG_TABLE_NAME Variable
    13. 13. Using WHEN Condition
    14. 14. Returning Values from Triggers
    15. 15. Listing Triggers

  44. Managing Triggers

    1. 1. Dropping Trigger
    2. 2. Disabling Trigger
    3. 3. Enabling Trigger
    4. 4. Disabling All Triggers
    5. 5. Enabling All Triggers
    6. 6. Using ENABLE ALWAYS
    7. 7. Using ENABLE REPLICA
    8. 8. Understanding Trigger Execution Order
    9. 9. Renaming Trigger
    10. 10. Viewing Trigger Definition

  45. Managing Transactions

    1. 1. Starting Transaction
    2. 2. Committing Transaction
    3. 3. Rolling Back Transaction
    4. 4. Using Autocommit Mode
    5. 5. Disabling Autocommit
    6. 6. Creating Savepoint
    7. 7. Rolling Back to Savepoint
    8. 8. Releasing Savepoint
    9. 9. Using COMMIT AND CHAIN
    10. 10. Using ROLLBACK AND CHAIN

  46. Understanding Isolation Levels

    1. 1. Understanding Read Committed
    2. 2. Understanding Repeatable Read
    3. 3. Understanding Serializable
    4. 4. Setting Transaction Isolation Level
    5. 5. Setting Default Isolation Level
    6. 6. Understanding Read Phenomena
    7. 7. Handling Serialization Failures
    8. 8. Using READ ONLY Transactions
    9. 9. Using READ WRITE Transactions
    10. 10. Understanding MVCC

  47. Working with Table Partitioning

    1. 1. Creating Partitioned Table
    2. 2. Using Range Partitioning
    3. 3. Using List Partitioning
    4. 4. Using Hash Partitioning
    5. 5. Creating Partitions
    6. 6. Setting Range Bounds
    7. 7. Setting List Values
    8. 8. Creating Default Partition
    9. 9. Attaching Existing Table
    10. 10. Detaching Partition
    11. 11. Dropping Partition
    12. 12. Creating Indexes on Partitioned Tables
    13. 13. Using Partition Pruning
    14. 14. Using Partition-wise Join
    15. 15. Using Partition-wise Aggregate

  48. Working with Table Inheritance

    1. 1. Creating Parent Table
    2. 2. Creating Child Table
    3. 3. Querying All Tables Including Children
    4. 4. Querying Parent Only
    5. 5. Understanding Constraint Inheritance
    6. 6. Understanding Unique Constraint Limitations
    7. 7. Overriding Parent Columns
    8. 8. Altering Parent Table
    9. 9. Understanding Inheritance vs Partitioning
    10. 10. Dropping Child Table

  49. Implementing Full-Text Search

    1. 1. Understanding tsvector Type
    2. 2. Understanding tsquery Type
    3. 3. Creating tsvector Column
    4. 4. Using to_tsvector Function
    5. 5. Using to_tsquery Function
    6. 6. Using plainto_tsquery Function
    7. 7. Using phraseto_tsquery Function
    8. 8. Using websearch_to_tsquery Function
    9. 9. Using Match Operator
    10. 10. Creating GIN Index
    11. 11. Using GiST Index
    12. 12. Ranking Search Results
    13. 13. Highlighting Results
    14. 14. Using Language Configurations
    15. 15. Creating Custom Text Search Configuration

  50. Advanced Full-Text Search

    1. 1. Using Text Search Dictionaries
    2. 2. Creating Custom Dictionary
    3. 3. Using Simple Dictionary
    4. 4. Using Snowball Dictionary
    5. 5. Using Synonym Dictionary
    6. 6. Using Thesaurus Dictionary
    7. 7. Combining Queries
    8. 8. Using Phrase Search
    9. 9. Using Prefix Matching
    10. 10. Using Weights
    11. 11. Filtering by Weight
    12. 12. Using Generated Columns for tsvector

  51. Managing Users and Roles

    1. 1. Creating Role
    2. 2. Creating User
    3. 3. Setting Password
    4. 4. Granting Login Privilege
    5. 5. Granting Superuser Privilege
    6. 6. Granting CREATEDB Privilege
    7. 7. Granting CREATEROLE Privilege
    8. 8. Granting REPLICATION Privilege
    9. 9. Setting Connection Limit
    10. 10. Setting Valid Until
    11. 11. Altering Role Properties
    12. 12. Renaming Role
    13. 13. Setting Role Password
    14. 14. Dropping Role
    15. 15. Listing Roles

  52. Managing Role Membership

    1. 1. Granting Role to User
    2. 2. Granting Multiple Roles
    3. 3. Granting with ADMIN OPTION
    4. 4. Revoking Role
    5. 5. Using Role Inheritance
    6. 6. Setting Current Role
    7. 7. Resetting Role
    8. 8. Checking Current User
    9. 9. Understanding Role Hierarchy
    10. 10. Using GROUP Roles

  53. Granting and Revoking Privileges

    1. 1. Granting Table Privileges
    2. 2. Granting Column-Level Privileges
    3. 3. Granting ALL Privileges
    4. 4. Granting on All Tables
    5. 5. Revoking Privileges
    6. 6. Granting Schema Privileges
    7. 7. Granting Database Privileges
    8. 8. Granting Function Privileges
    9. 9. Granting Sequence Privileges
    10. 10. Using WITH GRANT OPTION
    11. 11. Revoking with CASCADE
    12. 12. Setting Default Privileges
    13. 13. Checking Privileges

  54. Implementing Row-Level Security

    1. 1. Understanding Row-Level Security
    2. 2. Enabling RLS on Table
    3. 3. Creating Policy
    4. 4. Using FOR Commands
    5. 5. Using USING Expression
    6. 6. Using WITH CHECK Expression
    7. 7. Creating Permissive Policies
    8. 8. Creating Restrictive Policies
    9. 9. Using current_user in Policies
    10. 10. Using current_setting in Policies
    11. 11. Bypassing RLS
    12. 12. Forcing RLS for Table Owner
    13. 13. Disabling RLS
    14. 14. Dropping Policy
    15. 15. Listing Policies

  55. Managing Extensions

    1. 1. Listing Available Extensions
    2. 2. Listing Installed Extensions
    3. 3. Installing Extension
    4. 4. Creating Extension in Schema
    5. 5. Using uuid-ossp Extension
    6. 6. Using pgcrypto Extension
    7. 7. Using pg_trgm Extension
    8. 8. Using hstore Extension
    9. 9. Using citext Extension
    10. 10. Using ltree Extension
    11. 11. Using pg_stat_statements Extension
    12. 12. Using PostGIS Extension
    13. 13. Updating Extension
    14. 14. Dropping Extension

  56. Using Foreign Data Wrappers

    1. 1. Understanding Foreign Data Wrappers
    2. 2. Installing postgres_fdw Extension
    3. 3. Creating Foreign Server
    4. 4. Creating User Mapping
    5. 5. Creating Foreign Table
    6. 6. Importing Foreign Schema
    7. 7. Querying Foreign Tables
    8. 8. Using file_fdw
    9. 9. Using dblink Extension
    10. 10. Listing Foreign Servers
    11. 11. Listing User Mappings
    12. 12. Listing Foreign Tables
    13. 13. Dropping Foreign Table
    14. 14. Dropping Foreign Server

  57. Using Listen and Notify

    1. 1. Understanding LISTEN/NOTIFY
    2. 2. Subscribing to Channel
    3. 3. Sending Notification
    4. 4. Sending Notification with Payload
    5. 5. Using pg_notify Function
    6. 6. Receiving Notifications
    7. 7. Unsubscribing from Channel
    8. 8. Unsubscribing from All Channels
    9. 9. Listing Active Listeners
    10. 10. Using NOTIFY in Triggers
    11. 11. Understanding Payload Size Limit

  58. Working with Advisory Locks

    1. 1. Understanding Advisory Locks
    2. 2. Acquiring Session-Level Lock
    3. 3. Trying to Acquire Lock
    4. 4. Releasing Session Lock
    5. 5. Using Transaction-Level Locks
    6. 6. Using Shared Advisory Locks
    7. 7. Using Two-Integer Keys
    8. 8. Checking Lock Status
    9. 9. Unlocking All Session Locks
    10. 10. Using Advisory Locks for Coordination

  59. Working with System Catalogs

    1. 1. Understanding System Catalogs
    2. 2. Querying pg_class
    3. 3. Querying pg_attribute
    4. 4. Querying pg_constraint
    5. 5. Querying pg_index
    6. 6. Querying pg_database
    7. 7. Querying pg_namespace
    8. 8. Querying pg_proc
    9. 9. Querying pg_type
    10. 10. Querying pg_roles
    11. 11. Querying pg_tables
    12. 12. Querying pg_indexes
    13. 13. Using information_schema
    14. 14. Querying pg_settings

  60. Working with Logical Decoding

    1. 1. Understanding Logical Decoding
    2. 2. Configuring wal_level for Logical Decoding
    3. 3. Creating Logical Replication Slot
    4. 4. Using Output Plugins
    5. 5. Consuming Changes
    6. 6. Peeking at Changes
    7. 7. Dropping Replication Slot
    8. 8. Monitoring Replication Slots
    9. 9. Understanding Change Data Capture
    10. 10. Using Logical Decoding for Audit Trails

  61. Implementing Streaming Replication

    1. 1. Understanding Streaming Replication
    2. 2. Configuring Primary Server
    3. 3. Setting max_wal_senders
    4. 4. Setting wal_keep_size
    5. 5. Creating Replication User
    6. 6. Configuring pg_hba.conf
    7. 7. Creating Base Backup
    8. 8. Setting Up Standby Server
    9. 9. Configuring primary_conninfo
    10. 10. Starting Standby Server
    11. 11. Monitoring Replication Lag
    12. 12. Using Replication Slots
    13. 13. Promoting Standby
    14. 14. Understanding Synchronous vs Asynchronous Replication
    15. 15. Configuring synchronous_commit and synchronous_standby_names

  62. Implementing Logical Replication

    1. 1. Understanding Logical Replication
    2. 2. Configuring wal_level
    3. 3. Setting max_replication_slots and max_wal_senders
    4. 4. Creating Publication
    5. 5. Adding Tables to Publication
    6. 6. Publishing All Tables
    7. 7. Filtering Publication
    8. 8. Creating Subscription
    9. 9. Setting Subscription Connection
    10. 10. Setting Publication Name
    11. 11. Managing Initial Data Copy
    12. 12. Monitoring Subscriptions
    13. 13. Refreshing Publication
    14. 14. Dropping Subscription
    15. 15. Dropping Publication

  63. Backing Up with pg_dump

    1. 1. Creating Logical Backup
    2. 2. Dumping to File
    3. 3. Using Custom Format
    4. 4. Using Directory Format
    5. 5. Using Plain SQL Format
    6. 6. Using Tar Format
    7. 7. Dumping Specific Tables
    8. 8. Dumping Specific Schemas
    9. 9. Excluding Tables
    10. 10. Excluding Schemas
    11. 11. Dumping Schema Only
    12. 12. Dumping Data Only
    13. 13. Using Parallel Dump
    14. 14. Dumping All Databases
    15. 15. Dumping Only Roles

  64. Restoring from Backups

    1. 1. Restoring Plain SQL Dump
    2. 2. Restoring Custom Format
    3. 3. Restoring Directory Format
    4. 4. Using Parallel Restore
    5. 5. Restoring Specific Tables
    6. 6. Restoring Specific Schemas
    7. 7. Listing Contents
    8. 8. Using Restore List
    9. 9. Restoring Schema Only
    10. 10. Restoring Data Only
    11. 11. Cleaning Before Restore
    12. 12. Creating Database Before Restore
    13. 13. Handling Errors

  65. Implementing Point-in-Time Recovery

    1. 1. Understanding Point-in-Time Recovery
    2. 2. Enabling WAL Archiving
    3. 3. Configuring archive_command
    4. 4. Setting wal_level
    5. 5. Creating Base Backup
    6. 6. Stopping Base Backup
    7. 7. Restoring Base Backup
    8. 8. Creating recovery.signal File
    9. 9. Configuring restore_command
    10. 10. Setting recovery_target_time
    11. 11. Setting recovery_target_xid
    12. 12. Setting recovery_target_name
    13. 13. Creating Restore Points
    14. 14. Starting Recovery
    15. 15. Verifying Recovery

  66. Optimizing Query Performance

    1. 1. Using EXPLAIN
    2. 2. Using EXPLAIN ANALYZE
    3. 3. Using EXPLAIN (BUFFERS)
    4. 4. Using EXPLAIN (VERBOSE)
    5. 5. Understanding Scan Types
    6. 6. Understanding Join Methods
    7. 7. Understanding Node Types
    8. 8. Analyzing Costs
    9. 9. Identifying Bottlenecks
    10. 10. Using Index-Only Scans
    11. 11. Understanding Bitmap Heap Scans
    12. 12. Using Parallel Query Execution
    13. 13. Analyzing Join Order
    14. 14. Identifying Missing Indexes
    15. 15. Using EXPLAIN (FORMAT JSON, FORMAT YAML)

  67. Using VACUUM and ANALYZE

    1. 1. Understanding VACUUM
    2. 2. Running VACUUM on Table
    3. 3. Running VACUUM on Database
    4. 4. Using VACUUM FULL
    5. 5. Using VACUUM FREEZE
    6. 6. Using VACUUM ANALYZE
    7. 7. Running ANALYZE on Table
    8. 8. Running ANALYZE on Specific Columns
    9. 9. Understanding Autovacuum
    10. 10. Configuring Autovacuum
    11. 11. Monitoring VACUUM Progress
    12. 12. Checking Last Vacuum Time
    13. 13. Understanding Dead Tuples
    14. 14. Preventing Bloat

  68. Configuring Memory Settings

    1. 1. Setting shared_buffers
    2. 2. Setting work_mem
    3. 3. Setting maintenance_work_mem
    4. 4. Setting effective_cache_size
    5. 5. Setting temp_buffers
    6. 6. Setting wal_buffers (WAL buffer size)
    7. 7. Understanding Memory Context
    8. 8. Monitoring Memory Usage
    9. 9. Setting max_stack_depth
    10. 10. Setting huge_pages

  69. Configuring Connection Settings

    1. 1. Setting max_connections
    2. 2. Setting superuser_reserved_connections
    3. 3. Setting statement_timeout
    4. 4. Setting idle_in_transaction_session_timeout
    5. 5. Setting tcp_keepalives_idle
    6. 6. Setting tcp_keepalives_interval
    7. 7. Setting tcp_keepalives_count
    8. 8. Using Connection Pooling
    9. 9. Configuring listen_addresses
    10. 10. Setting port
    11. 11. Setting unix_socket_directories

  70. Configuring Write-Ahead Log

    1. 1. Understanding WAL
    2. 2. Setting wal_level
    3. 3. Setting fsync
    4. 4. Setting synchronous_commit
    5. 5. Setting wal_sync_method
    6. 6. Setting wal_buffers (WAL buffer size)
    7. 7. Setting checkpoint_timeout
    8. 8. Setting checkpoint_completion_target
    9. 9. Setting max_wal_size
    10. 10. Setting min_wal_size
    11. 11. Setting wal_compression
    12. 12. Setting full_page_writes
    13. 13. Monitoring WAL Activity

  71. Configuring Query Planner

    1. 1. Setting random_page_cost
    2. 2. Setting seq_page_cost
    3. 3. Setting cpu_tuple_cost
    4. 4. Setting cpu_index_tuple_cost
    5. 5. Setting cpu_operator_cost
    6. 6. Setting effective_io_concurrency
    7. 7. Setting enable_seqscan
    8. 8. Setting enable_indexscan
    9. 9. Setting enable_bitmapscan
    10. 10. Setting enable_hashjoin
    11. 11. Setting enable_mergejoin
    12. 12. Setting enable_nestloop
    13. 13. Setting enable_parallel_append, enable_parallel_hash
    14. 14. Setting max_parallel_workers_per_gather
    15. 15. Setting jit

  72. Monitoring Database Activity

    1. 1. Viewing Active Queries
    2. 2. Viewing Database Statistics
    3. 3. Viewing Table Statistics
    4. 4. Viewing Index Statistics
    5. 5. Checking Table Access Methods
    6. 6. Checking Table Modifications
    7. 7. Checking Index Usage
    8. 8. Monitoring Locks
    9. 9. Finding Blocked Queries
    10. 10. Killing Queries
    11. 11. Terminating Connections
    12. 12. Checking Database Size
    13. 13. Checking Table Size
    14. 14. Checking Index Size

  73. Using pg_stat_statements

    1. 1. Installing Extension
    2. 2. Configuring in postgresql.conf
    3. 3. Viewing Query Statistics
    4. 4. Finding Slow Queries
    5. 5. Finding Frequent Queries
    6. 6. Analyzing Query Performance
    7. 7. Checking I/O Statistics
    8. 8. Resetting Statistics
    9. 9. Setting Statement Tracking Level
    10. 10. Understanding Normalized Queries

  74. Working with Import and Export

    1. 1. Using COPY TO
    2. 2. Using COPY FROM
    3. 3. Specifying Delimiter
    4. 4. Using CSV Format
    5. 5. Using HEADER Option
    6. 6. Handling NULL Values
    7. 7. Using Quote Character
    8. 8. Using Escape Character (ESCAPE '\')
    9. 9. Using \copy Command
    10. 10. Using PROGRAM Option
    11. 11. Importing JSON Data
    12. 12. Exporting Query Results
    13. 13. Using STDIN and STDOUT
    14. 14. Handling Large Files

  75. Using psql Meta-Commands

    1. 1. Executing SQL Files
    2. 2. Listing Databases (\l, \list)
    3. 3. Listing Tables
    4. 4. Listing Schemas
    5. 5. Listing Views
    6. 6. Listing Functions
    7. 7. Describing Table
    8. 8. Viewing Permissions
    9. 9. Listing Indexes
    10. 10. Listing Sequences
    11. 11. Toggling Expanded Display
    12. 12. Setting Output Format
    13. 13. Timing Commands
    14. 14. Editing in External Editor
    15. 15. Using Variables

  76. Advanced psql Features

    1. 1. Writing Output to File
    2. 2. Reading Commands from File
    3. 3. Executing Shell Commands
    4. 4. Conditional Execution
    5. 5. Using Expressions in Conditionals
    6. 6. Showing Previous Query
    7. 7. Displaying Query Buffer
    8. 8. Executing from Buffer
    9. 9. Using psqlrc Configuration
    10. 10. Setting Prompt
    11. 11. Setting Null Display
    12. 12. Using Watch
    13. 13. Exiting psql

  77. Handling Errors and Exceptions

    1. 1. Understanding Error Codes
    2. 2. Using EXCEPTION Block in PL/pgSQL
    3. 3. Catching Specific Errors
    4. 4. Catching All Errors
    5. 5. Using RAISE Statement
    6. 6. Using RAISE with Format String
    7. 7. Using RAISE with HINT and DETAIL
    8. 8. Using ASSERT Statement
    9. 9. Using GET STACKED DIAGNOSTICS
    10. 10. Using SQLERRM Variable
    11. 11. Using SQLSTATE Variable
    12. 12. Configuring Error Logging
    13. 13. Creating Custom Exceptions

  78. Working with Cursors

    1. 1. Understanding Cursors
    2. 2. Declaring Cursor in PL/pgSQL
    3. 3. Opening Cursor
    4. 4. Fetching Rows
    5. 5. Using FETCH Directions
    6. 6. Closing Cursor
    7. 7. Using Cursor with Parameters
    8. 8. Using FOR Loop with Cursor
    9. 9. Using SCROLL Cursors
    10. 10. Using NO SCROLL Cursors
    11. 11. Using WITH HOLD Cursors
    12. 12. Using WITHOUT HOLD Cursors
    13. 13. Using MOVE Command
    14. 14. Fetching Multiple Rows

  79. Using Advanced SQL Features

    1. 1. Using VALUES Clause
    2. 2. Using TABLESAMPLE
    3. 3. Using SELECT INTO
    4. 4. Using Ordered-Set Aggregates
    5. 5. Using Hypothetical-Set Aggregates
    6. 6. Creating Custom Aggregate Functions
    7. 7. Using Range Types in Queries
    8. 8. Using Composite Types
    9. 9. Using Dollar Quoting
    10. 10. Using RETURNING with Data-Modifying CTEs
    11. 11. Using LATERAL in FROM Clause
    12. 12. Using ROWS FROM

  80. Working with Collations

    1. 1. Understanding Collations
    2. 2. Listing Available Collations
    3. 3. Setting Database Collation
    4. 4. Using COLLATE Clause in Query
    5. 5. Using COLLATE in Index
    6. 6. Creating Custom Collation
    7. 7. Using ICU Collations
    8. 8. Using Libc Collations
    9. 9. Using Case-Insensitive Collations
    10. 10. Understanding Collation Impact on Indexes
    11. 11. Understanding Collation Impact on ORDER BY
    12. 12. Altering Column Collation

  81. Implementing Database Security

    1. 1. Encrypting Data at Rest
    2. 2. Encrypting Connections
    3. 3. Using Password Encryption
    4. 4. Auditing Database Activity
    5. 5. Implementing Least Privilege Principle
    6. 6. Securing PostgreSQL Configuration
    7. 7. Restricting Network Access
    8. 8. Using SSL Certificates
    9. 9. Implementing Data Masking
    10. 10. Monitoring Security Events

  82. Troubleshooting Common Issues

    1. 1. Diagnosing Slow Queries
    2. 2. Resolving Lock Contention
    3. 3. Fixing Connection Issues
    4. 4. Handling Out of Memory Errors
    5. 5. Resolving Disk Space Issues
    6. 6. Fixing Replication Lag
    7. 7. Handling Transaction ID Wraparound
    8. 8. Debugging Function Errors
    9. 9. Resolving Permission Denied Errors
    10. 10. Analyzing PostgreSQL Logs