Tag Archives: plpgsql

Same SQL, Different Results: A Subtle Oracle vs PostgreSQL Migration Bug

Posted on January 30, 2026 by Deepak Mahto

The article examines issues encountered when migrating an Oracle application to PostgreSQL, particularly differences in operator precedence and implicit casting. A case study reveals how similar SQL expressions yield different results due to inherent database behaviors. The piece emphasizes the need for explicit intent in SQL to avoid silent data corruption and bugs post-migration. Continue reading →

Rate this:

Posted in Oracle Internal, Oracle to, Oracle to PG migration, postgresql | Tagged , , , , , | 2 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 1 – PL/pgSQL

Posted on June 8, 2024 by Deepak Mahto

PostgreSQL 17 Beta, released on May 23, 2024, introduces new features for database developers and migration engineers. One notable addition is the ability to declare array types directly based on underlying database objects, enhancing code readability and maintainability. These enhancements empower developers to leverage the full potential of PostgreSQL 17 in their projects. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , , , | 10 Comments

Conversion Gotchas: Implicit Conversion in Oracle to PostgreSQL Migration

Posted on May 3, 2024 by Deepak Mahto

Introduction – Implicit Conversion Oracle to PostgreSQL migration is a playground that uncovers and touches upon many database concepts, which are always intriguing and fun to explore. Implicit Conversion, i.e., imposing automatic conversion on data types to make them comparable … Continue reading →

Rate this:

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

PL\pgSQL Conversion Gotchas : Functions with Out parameter and return type.

Posted on April 23, 2024 by Deepak Mahto

If you’re fascinated by the internals of PL/pgSQL processing and want to handle it efficiently, especially if you’re migrating procedural logic from another database, be sure to check out the previous posts in the series on PL/pgSQL Conversion Gotchas – … Continue reading →

Rate this:

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

PL/pgSQL Conversion Gotchas: Cursors and Last Fetch.

Posted on April 12, 2024 by Deepak Mahto

Databases Cursors are one of the key components of development. We have been using them for traversing query results for processing based on business logic or functionality. In Code Conversion, Cursor code always needs to be ported either from PL\SQL … Continue reading →

Rate this:

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