Tag Archives: postgresql

Why Ora2Pg Should Be Your First Stop for PostgreSQL Conversion

Posted on March 31, 2026 by Deepak Mahto

The author emphasizes the importance of using Ora2pg for Oracle-to-PostgreSQL migrations, particularly for table DDL conversion. With over a decade of experience, they highlight Ora2pg’s robust configuration options that provide precise control. The post outlines specific features and transformation directives that enhance migration efficiency and accuracy, making complex migrations manageable. Continue reading →

Rate this:

Posted in Oracle to, Oracle to PG migration, postgresql | Tagged , , , , , , | Leave a comment

Oracle & SQL Server to PostgreSQL – Migration Tooling Gotchas No One Warns You About!

Posted on March 27, 2026 by Deepak Mahto

Migrations from Oracle and SQL Server to PostgreSQL often encounter overlooked challenges. The tools, while capable, can produce flawed code that compromises performance and functionality. Issues like improper data type mapping, silent errors from implicit casting, incomplete code, and extension lock-in require careful attention. Understanding these pitfalls is crucial for a successful migration. Continue reading →

Rate this:

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

PostgreSQL Sequence Reset: START WITH vs RESTART WITH vs SETVAL Explained

Posted on March 21, 2026 by Deepak Mahto

During an Oracle to PostgreSQL migration, a critical issue arose regarding sequence value resets. The tool ora2pg generated inadequate commands causing sequence values to mismatch post-cutover. A fix was implemented to replace the incorrect ALTER SEQUENCE START WITH command with RESTART WITH or setval() for accurate sequence resetting, crucial for successful migrations. Continue reading →

Rate this:

Posted in Oracle to, Oracle to PG migration, postgresql | Tagged , , , , , , , | Leave a comment

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

PostgreSQL Table Rename and Views – An OID Story

Posted on January 5, 2026 by Deepak Mahto

In a recent post-migration activity, a large table required a UUID column addition. Instead of using ALTER TABLE, which locks the table, a new table was created to improve performance. However, renaming can create dependency issues with views, as PostgreSQL uses OIDs for tracking. Properly managing dependencies is crucial to avoid operational risks. Continue reading →

Rate this:

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

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