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 – Part 1 and Part 2 In Oracle, we used to build multiple functionalities within procedural blocks, either in functions or procedures. In these blogs, we will explore some of the gotchas that we need to take care of. Migrating Functions with Out Parameters and Return Types Oracle allows functions to have arguments defined as out parameters and als ..read more

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 or TSQL to Pl\pgSQL, and some special considerations need to be incorporated as best practices to avoid surprises or functionality failures. If you are keen to understand cursors in Pl\pgSQL, will advice do checkout PostgreSQL official documents it covers most of the details in depth. In the blog, we will cover scenarios with cursors that differ f ..read more

One fun fact of Pl\pgSQL is that it’s an Extension in PostgreSQL that is created as default whenever we create any new databases. Converting databases, primarily those like Oracle, involves translating multiple functions, procedures, or packages written in PL/SQL to PostgreSQL with PL/pgSQL.During the conversion stage, we utilize conversion tools to automate a significant portion of the initial migration. However, it’s important to note that automatically converted code might not always follow best practices for PostgreSQL development. Converted code might not follow standard database developm ..read more

At PGConf India 2024, I delivered a lightning talk expressing my fondness for the psql terminal. During the presentation, I shared various tips and tricks that I personally cherish. The talk was well-received, particularly among those new to PostgreSQL, as they were pleasantly surprised by the insights and information shared. We have numerous sessions dedicated to PostgreSQL and its insights. However, I believe it’s also important to discuss the tools we utilize to interact with PostgreSQL. Personally, I have a strong affinity for PSQL due to its user-friendly interface and time-saving tips ac ..read more

A ROW expression allows you to construct ROW values, which can represent anonymous records, specific table row types, or custom composite types. Its uses include processing records within multiple expressions (using operators like =, <>, <, <=, >, or >=), evaluation with subqueries, and combining ROW values to build composite types. You can even export these to JSON format (using functions like rowtojson). Some key operations you can perform with a ROW constructor in Conversion or migration are : Building custom ROW types Expanding ROW types for internal processing Using wit ..read more

I would like to start the blog with an interesting fact: the language PL/pgSQL, which is used to build procedural code logic in PostgreSQL, is also an extension that is installed by default when you create a database. PostgreSQL is designed to be extensible, allowing us to load extensions that introduce additional features to functions as if they were built-in. CREATE EXTENSION is the most underrated Postgres feature — Adam Hendel (@adamhendel) December 13, 2023 Let’s begin with a list of extensions that I commonly use in almost all or most of migrations we facilitate for customers transi ..read more

Coming from an Oracle background and primarily involved in migrating customers to PostgreSQL, one feature that excited me was the elimination of the need for aliases in the FROM clause for sub-queries or value constructors. In PostgreSQL 16, it is now possible to define sub-queries without the need for aliases. In contrast to the previous version where aliases were mandatory, failure to provide them would result in query errors. In the migration process from Oracle to PostgreSQL, many conversion tools, such as ora2pg, automatically handle the variations in how aliases are supported across th ..read more

Code conversion is truly fascinating, as it sparks curiosity about how it is implemented on the preferred target and how it actually functions in the source code. Analytical functions empower SQL queries to handle complex requirements using windowing concepts. COUNT is an example of an aggregate function that can be used to retrieve count information with different partitions or as distinct within an analytical function. In PostgreSQL, if we attempt to use the Count aggregate function with Distinct within an analytical function, it will result in a failure. with alias1 as (select mod(a,2) c ..read more

If you’re a PostgreSQL user, you’re likely familiar with psql – the interactive terminal that connects you to your Postgresql databases. psql tool is more than just a basic utility. In fact, I consider it to be an essential asset for anyone working with PostgreSQL databases. With an impressive range of features, tips, and tricks, psql is go to tool to talk with PostgreSQL. It never fails to impress and surprise you with its incredible capabilities. I have compiled a list of the most useful psql features that I frequently use in my day-to-day interactions with PostgreSQL databases. Integration ..read more

In any database migration project, code conversion plays a critical role in achieving overall success and instilling confidence. During an Oracle to PostgreSQL migration, I am aware of conversion tools such as AWS Schema Conversion Tool and Ora2pg. These tools generally perform well in converting the dialect of the code. However, there are instances when we come across complex proprietary features or intricate code that prove to be challenging for automated conversion tools. In such cases, manual conversion becomes necessary to tackle these complexities effectively. Code conversion is the sub ..read more