Category Archives: postgresql

PostgreSQL Partition Pruning: The Role of Function Volatility

Posted on November 4, 2025 by Deepak Mahto

Improper volatility settings in PostgreSQL functions can significantly impact query performance, especially with partitioned tables. Functions like now() and statement_timestamp() (STABLE) allow efficient partition pruning, while volatile functions like clock_timestamp() hinder performance by necessitating full partition scans. Therefore, understanding function volatility is crucial for optimized SQL queries. Continue reading →

Rate this:

Posted in Oracle to PG migration, postgresql | Tagged , , , , , , , , , , | 4 Comments

Exploring PostgreSQL 18: A Developer’s Guide to New Features – Part 1: The COPY Command Gets More User-Friendly

Posted on September 28, 2025 by Deepak Mahto

PostgreSQL 18, released on September 25, 2024, enhances the COPY command with improved error handling through the REJECT_LIMIT option, allowing data loading to be controlled by limiting errors. This version also introduces additional logging options, aiding database developers in maintaining data integrity during file imports and improving overall data quality. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , | Leave a comment

PostgreSQL Case-Insensitive Search: Handling LIKE with Nondeterministic Collations

Posted on September 6, 2025 by Deepak Mahto

Migrating from Oracle or SQL Server to PostgreSQL often brings challenges with case-insensitive searches, since PostgreSQL doesn’t support them natively. Workarounds include nondeterministic collations or custom operators, though the recommended approach is to use the built-in “C” collation with ILIKE. Notably, PostgreSQL 18 is set to improve case-insensitive matching, making migrations smoother. Continue reading →

Rate this:

Posted in Oracle to PG migration, postgresql | Tagged , , , , , | 1 Comment

PostgreSQL 18 Beta Preview – Export or Amend Statistics with Ease

Posted on May 10, 2025 by Deepak Mahto

PostgreSQL 18 Beta introduces powerful enhancements to statistics management. For the first time, users can export, import, and modify planner statistics—making it easier to replicate production behavior in lower environments and fine-tune query performance without relying on actual data loads. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , | 4 Comments

Understanding Volatility in PL/pgSQL Functions: A Real-World Lesson

Posted on November 10, 2024 by Deepak Mahto

The PL/pgSQL language in PostgreSQL allows developers to create complex database functions with prescribed volatility categories: IMMUTABLE, STABLE, and VOLATILE. Selecting the right volatility is crucial for maintaining performance and data consistency, especially during migrations from other databases. Careful consideration prevents unexpected results and enhances application reliability. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , , | 2 Comments

PL/pgSQL Secrets: How Conditional Expressions Are Parsed and Evaluated Under the Hood.

Posted on November 2, 2024 by Deepak Mahto

In a recent discussion on PostgreSQL Slack, a user noted that a seemingly incomplete PL/pgSQL IF statement did not cause a syntax error. The condition is processed using a SELECT statement, allowing flexibility in evaluation. This insight enables developers to creatively incorporate SELECT elements into conditional statements without triggering errors. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , | 1 Comment

Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 7: pg_maintain Predefined Role for Maintenance.

Posted on September 23, 2024 by Deepak Mahto

PostgreSQL 17 introduces the pg_maintain role, enhancing access control over maintenance activities like VACUUM, ANALYZE, and REINDEX. This predefined role allows granular permission management, ensuring that only authorized users can perform critical maintainence operations. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , | 2 Comments

Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 5 : Correlated IN Clause Transformation

Posted on August 27, 2024 by Deepak Mahto

PostgreSQL 17 introduces impactful features, such as optimized IN Clause and Correlated Queries transformation, enhancing performance as compare to previous versions. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , | 5 Comments

Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 4: Enhanced Merge Command.

Posted on August 9, 2024 by Deepak Mahto

PostgreSQL 17 introduces the MERGE statement, which allows for single DML operations with varying conditions to INSERT, UPDATE, or DELETE based on a data source. This new functionality supports operations on rows that do not match conditions, alters the internal join strategy, and incorporates the RETURNING clause for greater flexibility. Additionally, the MERGE command now works with updatable views, expanding the possibilities for powerful and flexible data management. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , | 7 Comments