PL/pgSQL Conversion Gotchas: Cursors and Last Fetch.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
1w ago
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
Visit website
PL/pgSQL Conversion Gotchas: How to Handle Conflicting Variables.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
1w ago
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
Visit website
Why I Love psql – PostgreSQL Terminal.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
1M ago
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
Visit website
Uncovering and Exploring ROW Constructors in PostgreSQL.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
1M ago
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
Visit website
Key PostgreSQL Extensions Every Developer Must Know When Migrating from Oracle.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
4M ago
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
Visit website
PostgreSQL 16 Feature : Sub-Query Aliases and its usage in Code Conversion.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
7M ago
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
Visit website
Analytical Functions: Count of Distinct in Oracle vs. PostgreSQL
Database and Migration Insights » PostgreSQL
by Deepak Mahto
7M ago
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
Visit website
Get the most out of PostgreSQL using psql with must known features.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
7M ago
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
Visit website
Subtle Art of Code Conversion in Oracle to PostgreSQL Migration.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
7M ago
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
Visit website
Unleasing Boolean data type casting in PostgreSQL.
Database and Migration Insights » PostgreSQL
by Deepak Mahto
7M ago
Data type casting is an important aspect of working with databases. It is essential to understand the characteristics and behaviors of both implicit and explicit casting. PostgreSQL provides implicit casting for various operations, such as assignment and comparison, as well as explicit casting options. Using the psql command line, it is easy to access a list of all the supported casting options for each data type. For example, in PostgreSQL 14, the BOOLEAN data type offers the following type casts. With \dC we can get different casting available for a data type and whether it is implicit in n ..read more
Visit website

Follow Database and Migration Insights » PostgreSQL on FeedSpot

Continue with Google
Continue with Apple
OR