Migration at Scale: Achieve streamlined Oracle-to-Postgres Migrations with GitHub and DevOps

Introduction

Many organizations are realizing the benefits of migrating from Oracle to the cost-effective and powerful open-source PostgreSQL database. EDB Postgres Advanced Server (EPAS), with its Oracle compatibility features, makes this transition remarkably smooth. However, large-scale migrations still demand careful planning and the right tools.

In this blog post, we’ll outline a comprehensive migration strategy that leverages the power of DevOps practices with GitHub at its core, along with specialized EDB tools to streamline the process.

1. Initial Schema Conversion Using EDB Migration Toolkit and Migration Portal

The foundation of this approach lies in EDB’s Migration Toolkit and Migration Portal. Let’s outline the workflow:

• Assessment and Identification: The Migration Portal or Migration Toolkit analyzes your Oracle schema, highlighting potential incompatibilities and the need for manual adjustments.
• Schema Breakdown: Break your large schema into manageable SQL scripts (e.g., Tables.sql, Packages.sql, Procedures.sql, etc.) for focused conversion.
• Version Control Setup: Initialize a GitHub repository and store the initial Oracle schema in the ‘master’ branch for historical reference.
• Branch Creation for Conversion: Create separate branches from the ‘master’ to work on the conversion scripts generated by the Migration Portal. This modularizes your changes.

2. GitHub for Managing Ongoing Schema Changes

As your migration progresses, GitHub becomes your central hub for schema management:

• Branching for Isolation: Continue creating branches for specific schema modifications. This keeps your main development environment stable while changes are being tested and refined.
• Collaboration and Tracking: GitHub’s version control ensures seamless collaboration and a transparent history of all schema adjustments.

3. Automated Issue Detection and functional testing with GitHub Actions and SPL Check

Let’s introduce automation for error detection and quality control:

• GitHub Actions: Set up GitHub Actions to trigger automated checks whenever code is pushed to the repository.
• SPL Check: Integrate the EPAS ‘SPL Check extension’ into a GitHub Action to analyze SQL and PL/SQL code for Oracle compatibility issues. (See the provided code example below)

4. Creating Releases for Compatibility Assessment with EDB Migration Portal

To provide additional checkpoints in the migration process, let’s leverage the power of GitHub releases:

• Milestone Releases: As significant portions of the schema are converted, create releases within your GitHub repository.
• Compatibility Reassessment: Upload each release to the EDB Migration Portal. It will re-analyze the converted schema and provide a compatibility score against EDB Postgres Advanced Server. This helps track progress and proactively address issues.

5. Data Validation Using GitHub Actions and Containers

To ensure your converted procedures and packages function as expected, extend your GitHub Actions:

• Containerized Testing: Actions can spin up Docker containers with EPAS and sample data. These containers become isolated testing environments to validate database functionality.

6. Performance Tuning with CICD Pipeline and DBMS_PROFILER

Don’t overlook performance! Here’s how to focus on efficiency:

• CICD Integration: The EPAS ‘DBMS_PROFILER’ tool can be incorporated into your continuous integration and deployment (CICD) pipeline to gather performance data and pinpoint bottlenecks in the migrated database.

Adapting the Framework using ora2pg and plpgsql_check

The principles and workflow outlined in this blog post can be readily adapted if you’re using the popular ora2pg tool for your Oracle to Postgres migration. Here’s how the key steps would translate:

• Initial Conversion with ora2pg: Use ora2pg to perform the initial schema and data conversion from Oracle to Postgres.
• Compatibility Checks with plpgsql_check: Install the ‘plpgsql_check’ Postgres extension. This extension provides functions and analysis tools to validate the compatibility and correctness of your PL/pgSQL code specifically. Integrate it into your GitHub Actions alongside ora2pg output for automated checks.
• GitHub for Management and Automation: The core benefits of using GitHub for version control, branching, and GitHub Actions for automation remain fully applicable when using ora2pg.

7. Production Rollout and Switchover

After successful schema conversion, performance optimization, and thorough testing, it’s time to carefully orchestrate the production rollout:

• Production Environment Setup: Establish a production-ready Postgres environment adhering to best practices for architecture, backup/recovery strategies, and observability/monitoring tools.
• Change Data Capture (CDC): Implement a CDC solution (such as the EDB Replication Server) to continuously synchronize data from Oracle to your new Postgres database. This minimizes downtime during the cutover.
• Application Switchover: Once data migration is complete, switch your application to point to the Postgres database.
• Reverse Replication: Set up replication from Postgres back to Oracle. This acts as a safeguard in case unexpected issues arise after the switchover.
• Monitoring and Decommissioning: Closely monitor the new Postgres environment for a few weeks. If all goes smoothly, you can decommission the legacy Oracle database with confidence.

Important Note: This migration framework focuses primarily on the database aspect. Developers and DBAs should integrate application conversion, functional testing, and QA processes in parallel, ideally before the performance benchmark phase.

Conclusion

This comprehensive migration strategy, built upon DevOps principles and powerful tools, offers a reliable and efficient path for migrating from Oracle to Postgres. By emphasizing automation, collaboration, regular compatibility checks, an iterative refinement process, and a careful production rollout plan, you’ll streamline the migration and ensure a successful transition.

Stay tuned for future blog posts where we’ll dive into the implementation details of setting up GitHub Actions, utilizing extensions, and designing effective migration flow processes.

If you are reading this, please comment if you have followed this practice or if you have followed any other practices. Please comment and share your thoughts on which method had been best for your migration journey from Oracle to Postgres. Your insights and experiences will be valuable to the community!

Exploiting SQL/JSON Enhancements for Modern Workloads in PostgreSQL 16

The latest iteration of PostgreSQL, version 16, brings a suite of enhancements that bolster its capabilities with JSON data. These improvements not only align PostgreSQL more closely with the SQL/JSON standard but also offer significant optimizations that streamline and enhance working with JSON data. This evolution is a testament to PostgreSQL’s commitment to meeting the demands of modern, data-driven applications that leverage semi-structured data for a myriad of use cases.

In this post, we’ll delve into the SQL/JSON enhancements introduced in PostgreSQL 16, illustrate their application with code examples, and explore real-world scenarios where these enhancements can be particularly beneficial.

To fully leverage the SQL code examples mentioned in the blog post, let’s create a sample table schema (DDL) and insert some sample data for two tables, products and company_data. This setup will allow you to experiment with the new SQL/JSON enhancements in PostgreSQL 16 firsthand.

Table products

This table will store product details, including a unique identifier, the product name, attributes in JSON format, and additional product information in JSON format.

DDL for products:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    product_attributes JSONB,
    product_info JSONB
);

Sample Data for products:

INSERT INTO products (product_name, product_attributes, product_info) VALUES
('Laptop', '{"color": "#C0FF33", "memory": "16GB"}', '{"specifications": {"color": "#FF5733", "processor": "Intel i7", "storage": "512GB SSD"}}'),
('Smartphone', '{"color": "#FF5733", "memory": "8GB"}', '{"specifications": {"color": "#FF5733", "processor": "Snapdragon 888", "storage": "256GB UFS 3.1"}}');

Table company_data

This table will simulate employee data within a company, including an employee ID, their name, and their department.

DDL for company_data:

CREATE TABLE company_data (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department TEXT NOT NULL
);

Sample Data for company_data:

INSERT INTO company_data (employee_name, department) VALUES
('Alice Johnson', 'Engineering'),
('Bob Smith', 'Engineering'),
('Catherine Lee', 'Product'),
('David Brown', 'Sales');

Key PostgreSQL 16 SQL/JSON Improvements

Enhanced Numeric Literals in SQL/JSON Paths

PostgreSQL 16 introduces more readable path expressions by supporting hexadecimal, octal, and binary integers, along with the use of underscores in large numbers to improve readability. This feature is a boon for developers working with complex JSON data structures, allowing for clearer and more intuitive queries.

Example:

-- Using hexadecimal numeric literals for path expressions
SELECT product_info -> 'specifications' -> 'color' as color_code
FROM products
WHERE product_info -> 'specifications' ->> 'color' = '#FF5733';
 color_code 
------------
 "#FF5733"
 "#FF5733"
(2 rows)

New SQL/JSON Constructors

Creating JSON objects and arrays directly within SQL queries is now more straightforward with the introduction of JSON_OBJECT, JSON_ARRAY, JSON_OBJECT_AGG, and JSON_ARRAY_AGG. These constructors simplify the process of generating JSON data from relational data, enabling more efficient data transformation and aggregation.

Example:

SELECT department, JSON_OBJECT(employee_id::TEXT: employee_name) AS employees
FROM company_data;
 department  |        employees        
-------------+-------------------------
 Engineering | {"1" : "Alice Johnson"}
 Engineering | {"2" : "Bob Smith"}
 Product     | {"3" : "Catherine Lee"}
 Sales       | {"4" : "David Brown"}
(4 rows)

SELECT department, JSON_OBJECTAGG(employee_id::TEXT : employee_name) AS employees
FROM company_data
GROUP BY department;
 department  |                  employees                   
-------------+----------------------------------------------
 Product     | { "3" : "Catherine Lee" }
 Engineering | { "1" : "Alice Johnson", "2" : "Bob Smith" }
 Sales       | { "4" : "David Brown" }
(3 rows)

SELECT department, JSON_ARRAY(employee_name) AS employees
FROM company_data;
 department  |     employees     
-------------+-------------------
 Engineering | ["Alice Johnson"]
 Engineering | ["Bob Smith"]
 Product     | ["Catherine Lee"]
 Sales       | ["David Brown"]
(4 rows)

SELECT department, JSON_ARRAYAGG(employee_name) AS employees
FROM company_data GROUP BY department;
 department  |           employees            
-------------+--------------------------------
 Product     | ["Catherine Lee"]
 Engineering | ["Alice Johnson", "Bob Smith"]
 Sales       | ["David Brown"]
(3 rows)

SQL/JSON Object Checks

The addition of IS JSON predicates improves data integrity and validation by allowing queries to check if a column’s data adheres to expected JSON structures. This feature is particularly useful for ensuring data quality and consistency.

Example:

SELECT * 
FROM products
WHERE product_info IS JSON OBJECT;

 product_id | product_name |           product_attributes           |                                            product_info                                             
------------+--------------+----------------------------------------+-----------------------------------------------------------------------------------------------------
          1 | Laptop       | {"color": "#C0FF33", "memory": "16GB"} | {"specifications": {"color": "#FF5733", "storage": "512GB SSD", "processor": "Intel i7"}}
          2 | Smartphone   | {"color": "#FF5733", "memory": "8GB"}  | {"specifications": {"color": "#FF5733", "storage": "256GB UFS 3.1", "processor": "Snapdragon 888"}}
(2 rows)

SELECT employees IS JSON ARRAY AS json_object FROM (SELECT department, JSON_ARRAYAGG(employee_name) AS employees
FROM company_data GROUP BY department);
 json_object 
-------------
 t
 t
 t
(3 rows)

Vectorized JSON Parsing

Vector operations for JSON parsing in PostgreSQL 16 lead to significant performance gains, especially when processing large JSON documents. This enhancement underscores PostgreSQL’s capability to efficiently handle modern workloads that involve heavy JSON data manipulation.

The below snapshot demonstrates the performance difference between version 15 and 16, which is 43% faster.

PostgreSQL 15:
--------------
[postgres@ip-20-0-10-44 data]$ /usr/pgsql-15/bin/psql -p 5434
psql (15.6)
Type "help" for help.

postgres=# CREATE TABLE long_json_as_text AS
postgres-# with long as (
postgres(#         select repeat(description, 10) from pg_description pd
postgres(# )
postgres-# SELECT (select json_agg(row_to_json(long)) as t from long) from
postgres-# generate_series(1, 100);
SELECT 100
postgres=# VACUUM FREEZE long_json_as_text;
VACUUM
postgres=# \timing
Timing is on.
postgres=# SELECT 1 FROM long_json_as_text WHERE jsonb_typeof(t::jsonb) = 'not me';
 ?column? 
----------
(0 rows)

Time: 1003.056 ms (00:01.003)

PostgreSQL 16
--------------
postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 row)
postgres=# CREATE TABLE long_json_as_text AS
postgres-# with long as (
postgres(#         select repeat(description, 10) from pg_description pd
postgres(# )
postgres-# SELECT (select json_agg(row_to_json(long)) as t from long) from
postgres-# generate_series(1, 100);
SELECT 100
postgres=# VACUUM FREEZE long_json_as_text;
VACUUM
postgres=# \timing
Timing is on.
postgres=# SELECT 1 FROM long_json_as_text WHERE jsonb_typeof(t::jsonb) = 'not me';
 ?column? 
----------
(0 rows)

Time: 437.378 ms

Real-World Scenarios

Scenario 1: Data Validation and Cleanup

With the IS JSON checks, companies can ensure that data ingested from various sources into their PostgreSQL databases meets the expected JSON format. This is crucial for data quality and downstream processing, especially in ETL pipelines and data integration scenarios.

Scenario 2: Reporting and Analytics

The new JSON aggregation functions allow for dynamic creation of JSON objects and arrays directly within SQL queries, facilitating complex reporting and analytics tasks. For instance, aggregating customer feedback stored in JSON format by categories and generating reports directly from the database.

Scenario 3: Configuration and Preference Data

Applications often store user preferences or configuration settings in JSON format. The enhanced numeric literals and direct JSON construction capabilities make it easier to query and update these settings, improving application responsiveness and user experience.

Closing Thoughts

PostgreSQL 16’s SQL/JSON enhancements significantly improve the efficiency and performance of working with JSON data. These features enable developers to write more concise, readable, and efficient queries, leading to faster application development and better data management. As semi-structured data continues to play a critical role in application development, PostgreSQL reaffirms its position as a leading database choice for modern, data-intensive applications.

Whether you’re developing new applications or looking to migrate existing workloads, PostgreSQL 16 offers compelling features that can help streamline your operations and leverage JSON data more effectively. As the database continues to evolve, it’s clear that PostgreSQL is well-equipped to meet the challenges of today’s—and tomorrow’s—data landscape.

Harnessing the Power of Refdata Storage Extension in PostgreSQL with EDB

PostgreSQL, with its robust and versatile nature, has long been the go-to database for many organizations. Its continuous evolution introduces features that enhance performance and scalability. A notable advancement in this realm is the Refdata storage extension provided by EDB (EnterpriseDB), especially for PostgreSQL users. This blog post will explore the nuances of the Refdata storage extension and how it revolutionizes data handling in PostgreSQL.

What is Refdata Storage Extension?

Refdata, short for “Reference Data,” is a Table Access Method (TAM) introduced in PostgreSQL 12. It’s designed to optimize how table data is stored and accessed, particularly for tables containing mostly static data.

The Ideal Use Case: Static Data and High Concurrency

The Refdata extension shines in situations where a database table, such as a product catalog in an e-commerce system, is read-intensive and seldom modified. These tables often act as reference points for other tables in the database, for example, an ‘orders’ table referencing a ‘products’ table.

In a typical scenario, the ‘products’ table would be structured as follows:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

The Challenge: Concurrency and Performance

In high-concurrency environments, where multiple transactions are accessing the same row, PostgreSQL uses shared row locks and a mechanism called MultiXact (a mechanism for maintaining the list of active transactions accessing a row in shared mode) to manage these concurrent accesses. While effective, this can lead to overheads like cache misses and lock contention, affecting overall performance.

The Solution: Refdata TAM

Refdata TAM addresses these challenges by eliminating the overhead associated with shared row locks and MultiXacts for tables where it is applied. For instance, in our ‘products’ table example, DML operations become synchronous, and read operations are allowed to proceed concurrently, significantly enhancing performance.

Performance Benchmarks: A Real-World Perspective

Performance testing in a controlled environment, such as an EDB BigAnimal cluster on Amazon EC2 C5.4xlarge instance, reveals the tangible benefits of Refdata. Our engineer, Sravan Velagandula, recently performed a benchmark test focused on high-concurrency inserts. This test involved inserting data into the ‘orders’ table, while the ‘products’ table contained 20 rows. Utilizing pgbench, the test simulated a workload with 1000 clients and 64 threads over a duration of 5 minutes, aiming to insert rows into the orders table. This setup was designed to measure the performance impact of simultaneous insert operations under significant client and thread load. The results showed that using the Refdata extension led to a performance enhancement of over 100%, significantly outperforming the traditional method. This demonstrates the efficiency and effectiveness of the Refdata extension in handling complex database operations.

Conclusion: Refdata TAM as a Game Changer

For PostgreSQL users, the Refdata storage extension marks a significant advancement. This extension is especially advantageous for applications that frequently access tables containing mostly static data in shared mode, such as reference data. This setup ensures efficient handling and querying of data that does not change often but is regularly referenced by other parts of the application.

By implementing Refdata TAM, organizations can achieve more efficient data processing, reduce contention and lock-related overheads, and improve overall transaction throughput. This is particularly crucial in systems where performance and scalability are pivotal, such as in financial services, e-commerce platforms, and large-scale content management systems.

Key Advantages:

1. Enhanced Performance: By reducing the overhead associated with shared row locks and MultiXacts, the Refdata extension significantly boosts the performance of read-heavy tables.
2. Concurrency Management: It efficiently manages high-concurrency scenarios, ensuring smoother operations without the typical performance bottlenecks.
3. Optimized Resource Utilization: Less contention and overhead mean better utilization of hardware resources, leading to cost savings in large-scale deployments.

Who Should Consider Refdata TAM?

The Refdata extension is ideal for database architects and administrators managing PostgreSQL databases with tables that:

• Serve primarily as reference data for other tables.
• Experience high read access patterns with minimal updates or deletions.
• Are crucial for the performance of high-concurrency applications.

Getting Started with Refdata in EDB BigAnimal

EDB BigAnimal offers an intuitive and straightforward way to implement the Refdata storage extension. Database administrators can easily configure their PostgreSQL databases to leverage Refdata TAM for suitable tables, ensuring they get the most out of their database’s performance capabilities.

Final Thoughts

The Refdata storage extension is a testament to PostgreSQL’s and EDB’s commitment to continuous innovation and performance optimization. By understanding and leveraging this extension, organizations can take a significant step towards more efficient, scalable, and high-performing database operations. As the data landscape continues to evolve, tools like Refdata ensure that PostgreSQL remains at the forefront, meeting the demands of modern applications and systems.

Remember: While Refdata TAM brings considerable performance improvements, it’s essential to assess your specific database usage patterns and requirements to determine its suitability for your scenario. With the right implementation, Refdata can be a transformative addition to your PostgreSQL database strategy.

Bluefin for PostgreSQL: Revolutionizing Immutable Storage in Database Management

In the ever-evolving world of database management, the Bluefin extension for PostgreSQL stands out as a revolutionary storage extension, especially for those dealing with time-series data prevalent in IoT and monitoring applications. This blog post delves into the intricacies of Bluefin, exploring its unique features, advantages, and various use cases that benefit from its innovative approach to data management.

The Philosophy Behind Bluefin’s Design

The core design principle of Bluefin is straightforward yet impactful. By intentionally disallowing UPDATE and DELETE operations, Bluefin adopts a significantly smaller tuple header. While this might initially seem limiting, it’s a strategic move that reaps multiple benefits. Tuples in Bluefin are stored as compressed deltas of other tuples, and pages are eagerly frozen once full. This results in Bluefin tables being append-only, which remarkably increases the density of tuples per page, thereby accelerating read operations.

Table Partitioning: A Necessary Strategy

In absence of DELETE operations, Bluefin relies on table partitioning. This approach enables users to efficiently manage data, particularly in pruning older data. Dropping time-range-based older partitions keeps the database streamlined and effective.

Example of Creating a Partition Table:

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT NOT NULL,
    value FLOAT
)
PARTITION BY RANGE (time);
CREATE TABLE

CREATE TABLE sensor_data_2024_01 PARTITION OF sensor_data
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01') USING bluefin;
CREATE TABLE

CREATE TABLE sensor_data_2024_02 PARTITION OF sensor_data
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01') USING bluefin;
CREATE TABLE

INSERT INTO sensor_data (time, sensor_id, value) 
VALUES 
    ('2024-01-15 08:00:00', 1, 23.4),
    ('2024-01-15 08:05:00', 2, 24.1),
    ('2024-02-20 09:30:00', 1, 22.8);
INSERT 0 3

SELECT * FROM sensor_data WHERE sensor_id = 1;
           time            | sensor_id | value 
---------------------------+-----------+-------
 15-JAN-24 08:00:00 +00:00 |         1 |  23.4
 20-FEB-24 09:30:00 +00:00 |         1 |  22.8
(2 rows)

Targeted for Newer PostgreSQL Versions

It’s crucial to note that Bluefin is tailored for database versions 15 and onward, as it utilizes features introduced in PostgreSQL version 15.

Diverse Applications Across Industries

The use of immutable storage systems like Bluefin spans various sectors, each leveraging its unique features:

1. Audit Trails and Compliance: In finance and healthcare, Bluefin ensures unaltered, traceable data, crucial for audit trails.
2. Data Archiving: Bluefin’s immutable storage is ideal for legal or historical data, maintaining original data integrity.
3. WORM Storage: In legal and media sectors, Bluefin’s Write-Once-Read-Many approach safeguards copyrights and records.
4. Security and Ransomware Protection: Its immutability makes Bluefin a defense against attacks, securing critical backups.
5. Blockchain and Distributed Ledgers: Bluefin underpins the integrity of blockchain technology through data immutability.
6. Data Warehousing: It provides a consistent data pool for reliable historical data analysis.
7. Regulatory Data Retention: Bluefin meets the demands of regulations requiring certain data types to be retained unchanged.
8. Scientific Research and Data Analysis: In research, Bluefin ensures the stability and reliability of data over time.

Benchmark Results Showcasing Bluefin’s Capability

1. A Bluefin partitioned table is 57.1% smaller than a conventional Heap table.

• 

  Table Size Comparison (Heap Vs. Bluefin)

2. With this reduction in size, Bluefin delivers a substantial performance boost, achieving 14.2 times more Transactions Per Second (TPS) for specific workloads.

[enterprisedb@ip-20-0-10-44 log]$ pgbench -S -c 20 -T 60 non_blufin_db
pgbench (16.1.0, server 16.1.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 800
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 228701
number of failed transactions: 0 (0.000%)
latency average = 5.241 ms
initial connection time = 78.467 ms
tps = 3816.033484 (without initial connection time)

[enterprisedb@ip-20-0-10-44 log]$ pgbench -S -c 20 -T 60 blufin_db
pgbench (16.1.0, server 16.1.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 800
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 3232332
number of failed transactions: 0 (0.000%)
latency average = 0.371 ms
initial connection time = 66.370 ms
tps = 53931.296117 (without initial connection time)

Conclusion: Bluefin – A Paradigm Shift in PostgreSQL Data Management

In sum, the Bluefin Storage Extension transcends being merely an add-on; it represents a paradigm shift in how data integrity and efficiency are approached in database management. Its broad applicability across crucial sectors, where data immutability is essential, positions Bluefin not just as a feature but as a necessity. For PostgreSQL users, Bluefin is undoubtedly a game-changer, redefining the limits of secure, efficient, and reliable data storage and management.

For more information on Bluefin Storage, please refer to the following link

https://www.enterprisedb.com/docs/pg_extensions/advanced_storage_pack/#bluefin

Enhanced Developer Experience in EPAS 16 with Oracle Compatibility

Introduction

EDB (EnterpriseDB) has always been at the forefront of enhancing PostgreSQL for enterprise needs. With the release of EPAS 16, they have taken a significant leap in ensuring Oracle compatibility and improving the developer experience. In this post, we’ll delve into four key features – DBTIMEZONE, SESSIONTIMEZONE, TO_TIMESTAMP_TZ, and DBMS_UTILITY.EXPAND_SQL_TEXT – that stand out in EPAS 16, offering a more seamless transition for Oracle developers and a more intuitive environment for all users.

1. DBTIMEZONE Function

The DBTIMEZONE function in EPAS 16 is a welcomed addition for developers migrating from Oracle. It returns the time zone of the database, a crucial element for applications dealing with multiple time zones.

Example

SELECT DBTIMEZONE FROM DUAL;
 dbtimezone 
------------
 +00:00
(1 row)

This query returns the database’s time zone, simplifying time zone management in global applications.

2. SESSIONTIMEZONE Function:

Similarly, SESSIONTIMEZONE is another function that enhances the EPAS environment by returning the session’s current time zone setting. This is particularly useful in user-specific time zone configurations.

Example:

SELECT SESSIONTIMEZONE FROM dual;
 sessiontimezone 
-----------------
 UTC
(1 row)

This shows the current session’s time zone, aiding developers in debugging and designing user-centric applications.

3. TO_TIMESTAMP_TZ Function

EPAS 16 simplifies the TO_TIMESTAMP_TZ function by allowing it to accept just one argument. This makes converting string data to timestamp with time zone much more straightforward.

Example:

SELECT TO_TIMESTAMP_TZ('20-MAR-20 04:30:00.123456 PM +03:00') FROM DUAL;
        to_timestamp_tz        
-------------------------------
 2020-03-20 13:30:00.123456+00
(1 row)

This converts the provided string into a timestamp with the time zone.

4. DBMS_UTILITY.EXPAND_SQL_TEXT subprogram:

EPAS 16 introduces the DBMS_UTILITY.EXPAND_SQL_TEXT procedure, a powerful tool for developers. It provides a way to analyze and expand SQL texts for better understanding and optimization.

Example

Below is an example in DBMS_UTILITY.EXPAND_SQL_TEXT expanded normal SQL to show the exact table behind the view.

SELECT pg_get_viewdef('public.salesemp');
              pg_get_viewdef               
-------------------------------------------
  SELECT empno,                           +
     ename,                               +
     hiredate,                            +
     sal,                                 +
     comm                                 +
    FROM emp                              +
   WHERE ((job)::text = 'SALESMAN'::text);


edb=# DECLARE
   out_sql TEXT;
BEGIN
  DBMS_UTILITY.EXPAND_SQL_TEXT(input_sql_text => 'SELECT * FROM salesemp', output_sql_text => out_sql);
  DBMS_OUTPUT.PUT_LINE(out_sql);
END;

 SELECT empno,
    ename,
    hiredate,
    sal,
    comm
   FROM ( SELECT emp.empno,
            emp.ename,
            emp.hiredate,
            emp.sal,
            emp.comm
           FROM emp
          WHERE ((emp.job)::text = 'SALESMAN'::text)) salesemp

EDB-SPL Procedure successfully completed

Conclusion:

EPAS 16 is making strides in bridging the gap between Oracle and PostgreSQL, particularly in terms of compatibility and ease of development. These four features not only ease the transition for Oracle developers but also enhance the overall developer experience in PostgreSQL. With these advancements, EPAS continues to strengthen its position as a robust, enterprise-level database solution, catering to a diverse range of development needs.
As EPAS evolves, it’s exciting to think about what further enhancements and capabilities future releases will bring to the world of database management and development.

Unlocking the Power of EPAS 16: Exploring New Advanced Features with Examples

The release of EDB Postgres Advanced Server (EPAS) 16 brings a suite of exciting new features, each designed to enhance the functionality and efficiency of database operations. In this blog, we’ll explore five notable additions, providing working code examples and discussing the benefits of each.

1. FETCH cursor BULK COLLECT INTO

Feature: The FETCH cursor BULK COLLECT INTO feature allows for the efficient fetching of multiple rows into a collection in a single operation.

Example:

DECLARE
   TYPE EmpList IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
   emp_collection EmpList;
   CURSOR emp_cursor IS SELECT ROWID, * FROM employee;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor BULK COLLECT INTO emp_collection LIMIT 3 ;
   CLOSE emp_cursor;

   -- Process the collection
   FOR i IN 1..emp_collection.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_collection(i).name);
   END LOOP;
END;

Employee Name: Employee_1
Employee Name: Employee_2
Employee Name: Employee_3

EDB-SPL Procedure successfully completed

Benefits:

1. Efficiency in Data Retrieval: Fetches large datasets quickly.
2. Reduced Server Load: Minimizes the number of round-trips between client and server.

2. NANVL, LNNVL, and DUMP

Features:

• NANVL: Replaces NaN (not-a-number) values with a specified value.
• LNNVL: Provides a logical negation of a condition, particularly useful with NULLs.
• DUMP: Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value.

Examples:

SELECT NANVL(comm, 0) FROM emp WHERE empno=7839;
 nanvl 
-------
     0
(1 row)

SELECT * FROM emp WHERE LNNVL(sal > 1000);
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
  7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 |      |     30
(2 rows)

SELECT DUMP('Hello, World!') FROM dual;
                             dump                              
---------------------------------------------------------------
 Typ=25 Len=13: 72,101,108,108,111,44,32,87,111,114,108,100,33
(1 row)

Benefits: These functions enhance data handling capabilities, especially in complex analytical queries. NANVL and LNNVL improve the handling of special or null values, while DUMP aids in debugging and understanding data representation.

3. Push Down Aggregate (Upper Relation) Operations

Feature: This optimization feature allows aggregate operations to be pushed down to a lower level in the query plan.

Example:

EXPLAIN ANALYZE SELECT SUM(id) FROM employee@xe_oracle;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=32) (actual time=4.503..4.549 rows=1 loops=1)
   Relations: Aggregate on (_dblink_employee_2 employee)
   Remote Query: SELECT sum(id) FROM employee
 Planning Time: 3.580 ms
 Execution Time: 5.091 ms
(5 rows)

Benefits: The push-down optimization can lead to significant performance improvements by reducing the amount of data that needs to be processed and transferred within the database engine.

4. NLS_CHARSET Functions

NLS_CHARSET provides functions to determine the character set used by the database, crucial for handling multilingual data.

Example:

SELECT NLS_CHARSET_NAME(6);
 nls_charset_name 
------------------
 UTF8
(1 row)

SELECT NLS_CHARSET_ID('UTF8');
 nls_charset_id 
----------------
              6
(1 row)

5. Procedure Synonyms

Example:

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello from my procedure!');
END;
CREATE PROCEDURE

CREATE SYNONYM proc_synonym FOR my_procedure;
CREATE SYNONYM

BEGIN
   proc_synonym;
END;
Hello from my procedure!

EDB-SPL Procedure successfully completed

Benefits:

• Simplified Referencing: Easier to call procedures from different schemas.
• Improved Maintainability: Changes to procedure paths only need to be updated in the synonym.

Conclusion

These features showcase EDB’s powerful capabilities in handling complex data operations efficiently. By integrating these functionalities into your EDB database strategies, you can significantly enhance the performance and flexibility of your data management processes.

Exploring New DBMS_SQL Subprograms in EDB Postgres Advanced Server 16

EDB Postgres Advanced Server 16 has brought a suite of new subprograms in the DBMS_SQL package, enhancing the capabilities and flexibility for database developers and DBAs. In this blog post, we’ll explore some of these additions, including describe_columns3, describe_columns2, to_cursor_number, to_refcursor, define_array, bind_array, bind_array_raw, define_column_rowid, and column_value_rowid. We’ll delve into their functionalities, provide working code examples, and discuss the benefits these functions offer.

describe_columns3 and describe_column2

These functions provide detailed information about the columns in a query’s result set.

• describe_columns3: Offers an extended description, including precision and scale of columns.
• describe_columns2: An enhanced version of the original describe_columns, providing additional details compared to its predecessor.

Benefits: Enhanced metadata retrieval for dynamic SQL, aiding in precise result set processing and validation.

to_cursor_number and to_refcursor

• to_cursor_number: Converts a ref cursor to a DBMS_SQL cursor number.
• to_refcursor: Converts a DBMS_SQL cursor number to a ref cursor.

Benefits: Seamless conversion between cursor types enhances compatibility and integration with different EDB-SPL and SQL code parts.

define_array, bind_array and bind_array_raw

• define_array: Defines an array for bulk fetch operations.
• bind_array/bind_array_raw: Binds an array for bulk DML operations.

Benefits: Facilitates bulk operations for improved performance and efficiency in handling large data sets.

define_column_rowid and column_value_rowid

• define_column_rowid: Defines a column of type ROWID in a query’s result set.
• column_value_rowid: Retrieves the ROWID value from a specified column in the result set.

Benefits: Allows easy retrieval and handling of ROWID values, crucial for row identification and manipulation.

DBMS_SQL Subprograms’ Examples

Following are some examples of the above subprogram with output in EDB-SPL

describe_column3

DECLARE
    cursor_id INTEGER;
    column_count INTEGER;
    desc_tab DBMS_SQL.DESC_TAB3;
BEGIN
    -- Open a cursor
    cursor_id := DBMS_SQL.OPEN_CURSOR;

    -- Parse a query
    DBMS_SQL.PARSE(cursor_id, 'SELECT empno, ename, sal FROM emp', DBMS_SQL.NATIVE);

    -- Describe the columns
    DBMS_SQL.DESCRIBE_COLUMNS3(cursor_id, column_count, desc_tab);

    -- Loop through the described columns
    FOR i IN 1 .. column_count LOOP
        DBMS_OUTPUT.PUT_LINE('Column ' || i || ':');
        DBMS_OUTPUT.PUT_LINE('Name: ' || desc_tab(i).col_name);
        DBMS_OUTPUT.PUT_LINE('Data Type: ' || desc_tab(i).col_type);
        DBMS_OUTPUT.PUT_LINE('Max Length: ' || desc_tab(i).col_max_len);
        -- Other column attributes can be accessed similarly
    END LOOP;

    -- Close the cursor
    DBMS_SQL.CLOSE_CURSOR(cursor_id);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        -- Close the cursor if open
        IF DBMS_SQL.IS_OPEN(cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(cursor_id);
        END IF;
END;

Column 1:
Name: empno
Data Type: 2
Max Length: 22
Column 2:
Name: ename
Data Type: 1
Max Length: 10
Column 3:
Name: sal
Data Type: 2
Max Length: 22

EDB-SPL Procedure successfully completed

to_cursor_number and to_refcursor

DECLARE
  ref_cursor SYS_REFCURSOR;
  cursor_number INTEGER;
  query VARCHAR2(1000);
  a_employee_id emp.empno%TYPE;
  a_name emp.ename%TYPE;
BEGIN
  -- Open a REF CURSOR for a query
  query := 'SELECT empno, ename FROM emp WHERE deptno = 10';
  OPEN ref_cursor FOR query; -- Assuming department_id = 10

  -- Convert REF CURSOR to DBMS_SQL cursor number
  cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);

  -- Define columns for the cursor
  DBMS_SQL.DEFINE_COLUMN(cursor_number, 1, a_employee_id);
  DBMS_SQL.DEFINE_COLUMN(cursor_number, 2, a_name);

  -- Fetch rows from the cursor
  WHILE DBMS_SQL.FETCH_ROWS(cursor_number) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(cursor_number, 1, a_employee_id);
    DBMS_SQL.COLUMN_VALUE(cursor_number, 2, a_name);
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || a_employee_id || ' Employee Name: ' || a_name);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(cursor_number);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(cursor_number) THEN
      DBMS_SQL.CLOSE_CURSOR(cursor_number);
    END IF;
    RAISE;
END;

Employee ID: 7782 Employee Name: CLARK
Employee ID: 7839 Employee Name: KING
Employee ID: 7934 Employee Name: MILLER

EDB-SPL Procedure successfully completed

DECLARE
  ref_cursor SYS_REFCURSOR;
  cursor_number INTEGER;
BEGIN
  OPEN ref_cursor FOR SELECT * FROM emp;
  cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
  -- Convert back if needed
  ref_cursor := DBMS_SQL.TO_REFCURSOR(cursor_number);
END;

EDB-SPL Procedure successfully completed

define_column_rowid, column_value_rowid and describe_column2

SET default_with_rowids TO ON;

CREATE TABLE EMPLOYEE AS SELECT LEVEL AS ID, 'Employee_'||LEVEL AS NAME, LEVEL*1000 AS SALARY FROM DUAL CONNECT BY LEVEL <= 5;

DECLARE
  cur NUMBER;
  desc_t DBMS_SQL.DESC_TAB2;
  col_cnt NUMBER;
  status NUMBER;
  row_id ROWID;
  name VARCHAR2(100);
  salary NUMBER;
  name_arr DBMS_SQL.VARCHAR2_TABLE;
BEGIN
  -- Open a cursor
  cur := DBMS_SQL.OPEN_CURSOR;

  -- Parse the SQL statement
  DBMS_SQL.PARSE(cur, 'SELECT ROWID, NAME, SALARY FROM EMPLOYEE', DBMS_SQL.NATIVE);

  -- Describe the columns
  DBMS_SQL.DESCRIBE_COLUMNS2(cur, col_cnt, desc_t);

  -- Define the output columns
  DBMS_SQL.DEFINE_COLUMN_ROWID(cur, 1, row_id);
  DBMS_SQL.DEFINE_COLUMN(cur, 2, name, 100);
  DBMS_SQL.DEFINE_COLUMN(cur, 3, salary);

  -- Execute the SQL statement
  status := DBMS_SQL.EXECUTE(cur);

  -- Fetch the rows and print the row IDs, names, and salaries
  LOOP
    IF DBMS_SQL.FETCH_ROWS(cur) = 0 THEN
      EXIT;
    END IF;

    DBMS_SQL.COLUMN_VALUE_ROWID(cur, 1, row_id);
    DBMS_SQL.COLUMN_VALUE(cur, 2, name);
    DBMS_SQL.COLUMN_VALUE(cur, 3, salary);
    DBMS_OUTPUT.PUT_LINE('Row ID: ' || row_id || ', Name: ' || name || ', Salary: ' || salary);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
Row ID: 1, Name: Employee_1, Salary: 1000
Row ID: 2, Name: Employee_2, Salary: 2000
Row ID: 3, Name: Employee_3, Salary: 3000
Row ID: 4, Name: Employee_4, Salary: 4000
Row ID: 5, Name: Employee_5, Salary: 5000

EDB-SPL Procedure successfully completed

define_array, bind_array and bind_array_raw

CREATE TABLE PERSON AS SELECT LEVEL AS ID, 'Person_'||LEVEL AS NAME FROM DUAL CONNECT BY LEVEL <= 5;

DECLARE
  p_ids dbms_sql.number_table;
  c NUMBER;
  dummy NUMBER;
  p_name VARCHAR2(100);
BEGIN
  -- Define the array of IDs
  p_ids(1) := 2;
  p_ids(2) := 3;
  p_ids(3) := 4;

  -- Open a cursor
  c := DBMS_SQL.OPEN_CURSOR;

  -- Parse the SQL statement
  DBMS_SQL.PARSE(c, 'SELECT name FROM PERSON WHERE id IN (:num_array)', DBMS_SQL.NATIVE);

  -- Define the output column
  dbms_sql.define_column(c, 1, p_name, 100);

  -- Bind the array to the SQL statement
  DBMS_SQL.BIND_ARRAY(c, ':num_array', p_ids);

  -- Execute the SQL statement
  dummy := DBMS_SQL.EXECUTE(c);

  -- Fetch the rows and print the names
  LOOP
    EXIT WHEN dbms_sql.fetch_rows(c) <= 0;
    dbms_sql.column_value(c, 1, p_name);
    dbms_output.put_line(p_name);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(c);
END;
Person_4

EDB-SPL Procedure successfully completed

Conclusion

The introduction of these new subprograms in EDB Postgres Advanced Server 16’s DBMS_SQL package is a testament to the evolving nature of database technology. They offer greater flexibility, efficiency, and precision in handling dynamic SQL operations, especially where complex data types and bulk processing are involved. By integrating these functionalities, developers and DBAs can significantly enhance their database operations, paving the way for more advanced and optimized data handling solutions.

Harnessing the Power of UTL_FILE in EPAS 16: A Deep Dive into Enhanced File Handling Capabilities

In the realm of EDB Postgres Advanced Server (EPAS) 16 databases, the UTL_FILE package stands as a cornerstone for interacting with the file system from EDB-SPL. This package offers a versatile set of subprograms for file operations, making it an invaluable tool for developers and DBAs alike. In this blog post, we will explore the capabilities of new submodules in UTL_FILE of EPAS 16, such as put_nchar, fgetpos, get_line_nchar, fgetattr, putf_nchar, put_line_nchar, fseek, and fopen_nchar, complete with examples.

What is UTL_FILE?

UTL_FILE is a built-in EDB-SPL package that facilitates reading from and writing to files on the server’s file system. It provides a collection of subprograms that can manage files, allowing for operations like file creation, reading, writing, and positioning.

Why is UTL_FILE Important?

The significance of UTL_FILE lies in its ability to bridge the gap between the database and the file system. It allows for:

1. File System Integration: UTL_FILE bridges the gap between the EPAS and the server’s file system, allowing for more integrated and efficient data processing.
2. Automation and Reporting: It’s essential for generating reports, logging, and automating file-based data exchange processes.
3. Efficient Data Export/Import: Smoothly handle data transfers between the database and external files.
4. Enhanced File Manipulation: Read, write, and modify files directly from the database.

How UTL_FILE is Changing the Landscape

The enhanced UTL_FILE package in EPAS 16 for developers and DBAs facilitates a more seamless and integrated approach to handling external files, especially in a multilingual context. This ability to directly read from and write to files using a variety of character sets, including NLS data, greatly simplifies data management tasks that involve external file interaction. The package’s capabilities in handling file attributes and positions further empower users to write more efficient and sophisticated file-handling EDB-SPL.

Exploring the Submodules with Examples

1. put_nchar

put_nchar writes a specified NCHAR string to a file.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
BEGIN
  file_handler := UTL_FILE.FOPEN_NCHAR('DIR', 'testfile.txt', 'W');
  UTL_FILE.PUT_NCHAR(file_handler, N'Hello, World!');
  UTL_FILE.FCLOSE(file_handler);
END;

2. fgetpos

fgetpos returns the current position of the file pointer.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';  
DECLARE
  file_handler UTL_FILE.FILE_TYPE;
  pos INTEGER;
BEGIN
  file_handler := UTL_FILE.FOPEN('DIR', 'testfile.txt', 'R');
  UTL_FILE.FGETPOS(file_handler, pos);
  DBMS_OUTPUT.PUT_LINE('Position: ' || pos);
  UTL_FILE.FCLOSE(file_handler);
END;

3. get_line_nchar

get_line_nchar reads a line of NCHAR text from a file.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
  line NVARCHAR2(100);
BEGIN
  file_handler := UTL_FILE.FOPEN_NCHAR('DIR', 'testfile.txt', 'R');
  UTL_FILE.GET_LINE_NCHAR(file_handler, line);
  DBMS_OUTPUT.PUT_LINE(line);
  UTL_FILE.FCLOSE(file_handler);
END;

3. fgetattr

fgetattr retrieves attributes of a file, such as its size, existence, and read/write permissions.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';  

DECLARE
  fexists BOOLEAN;
  file_len NUMBER;
  blocksize NUMBER;
BEGIN
  UTL_FILE.FGETATTR('DIR', 'testfile.txt', fexists, file_len, blocksize);
  IF fexists THEN
    DBMS_OUTPUT.PUT_LINE('File size: ' || file_len);
  ELSE
    DBMS_OUTPUT.PUT_LINE('File does not exist');
  END IF;
END;

5. putf_nchar

putf_nchar writes formatted NCHAR text to a file.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';  

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
BEGIN
  file_handler := UTL_FILE.FOPEN_NCHAR('DIR', 'testfile.txt', 'W');
  UTL_FILE.PUTF_NCHAR(file_handler, 'Name: %s, Age: %s', N'John', N'30');
  UTL_FILE.FCLOSE(file_handler);
END;

6. put_line_nchar

put_line_nchar writes a line of NCHAR text, followed by an end-of-line marker.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
BEGIN
  file_handler := UTL_FILE.FOPEN_NCHAR('DIR', 'testfile.txt', 'W');
  UTL_FILE.PUT_LINE_NCHAR(file_handler, N'Hello, World!');
  UTL_FILE.FCLOSE(file_handler);
END;

7. fseek

fseek moves the file pointer to a specified position.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
BEGIN
  file_handler := UTL_FILE.FOPEN('DIR', 'testfile.txt', 'R');
  UTL_FILE.FSEEK(file_handler, 100); -- Move pointer to 100th byte
  UTL_FILE.FCLOSE(file_handler);
END;

8. fopen_nchar

fopen_nchar opens a file for NCHAR data processing.

CREATE OR REPLACE DIRECTORY "DIR" AS '/tmp';

DECLARE
  file_handler UTL_FILE.FILE_TYPE;
BEGIN
  file_handler := UTL_FILE.FOPEN_NCHAR('DIR', 'testfile.txt', 'W');
  -- Perform file operations
  UTL_FILE.FCLOSE(file_handler);
END;

Conclusion

UTL_FILE in EPAS offers a powerful suite of tools for file operations within the database environment. By leveraging these submodules, developers and DBAs can perform a wide range of file manipulation tasks, enhancing the overall capabilities of EPAS. Whether it’s for data export, report generation, or handling complex file-based operations, UTL_FILE stands as a pivotal feature in the Postgres database landscape.

Exploring MULTISET INTERSECT and MULTISET EXCEPT in EDB Postgres Advanced Server 16: Enhancing Data Handling Capabilities

The latest iteration of EDB Postgres Advanced Server, version 16, has introduced some exciting features, significantly enhancing its capabilities. Among these new additions, the MULTISET INTERSECT and MULTISET EXCEPT operators stand out for their unique functionality and utility. This blog post will explore what these features are, their importance, and provide practical examples with working code to showcase their applications.

What Are MULTISET INTERSECT and MULTISET EXCEPT?

MULTISET INTERSECT and MULTISET EXCEPT are set operations that operate on nested tables – collections of rows treated as a single unit. These operators enable users to perform more complex set operations than those allowed by traditional SQL set operators.

1. MULTISET INTERSECT: This operator returns a nested table containing every element that is in both of the operand nested tables. It’s akin to finding a common ground between two sets of data.
2. MULTISET EXCEPT: Conversely, this operator returns a nested table that consists of elements from the first operand nested table, excluding those found in the second one. It effectively subtracts one set from another.

The Importance of These Features

These operators are particularly important in scenarios where relational data needs to be manipulated in a set-oriented manner. This is especially true for complex data processing tasks, such as those found in data analytics, data warehousing, and business intelligence applications. They provide:

• Enhanced Data Manipulation: Allow for more sophisticated and nuanced set operations directly in SQL.
• Simplified Queries: Reduce the complexity of queries that would otherwise require multiple steps and temporary storage.
• Improved Performance: Potentially more efficient execution as compared to manually coded set manipulations.
• Simplified Oracle Migration: Reduces the effort of implementing workarounds for this feature during migration from Oracle to Postgres.

Practical Examples with Working Code

Let’s explore some practical scenarios where MULTISET INTERSECT and MULTISET EXCEPT can be used, along with sample SQL code to demonstrate their application.

Example 1: Using MULTISET INTERSECT

Imagine we have two nested tables representing the course lists of two students, and we want to find the courses common to both students.

DECLARE
  TYPE CourseList IS TABLE OF VARCHAR2(100);
  student1_courses CourseList := CourseList('Math', 'Science', 'History');
  student2_courses CourseList := CourseList('Math', 'English', 'History');
  common_courses CourseList;
BEGIN
  common_courses := student1_courses MULTISET INTERSECT student2_courses;
  DBMS_OUTPUT.PUT_LINE('Common Courses: ' || common_courses.COUNT);
  FOR i IN 1..common_courses.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(common_courses(i));
  END LOOP;
END;

Output:

Common Courses: 2
Math
History

EDB-SPL Procedure successfully completed
edb=# 

Example 2: Using MULTISET EXCEPT

Now, let’s say we want to find out which courses student1 is taking that student2 is not.

DECLARE
  TYPE CourseList IS TABLE OF VARCHAR2(100);
  student1_courses CourseList := CourseList('Math', 'Science', 'History');
  student2_courses CourseList := CourseList('Math', 'English', 'History');
  unique_courses CourseList;
BEGIN
  unique_courses := student1_courses MULTISET EXCEPT student2_courses;
  DBMS_OUTPUT.PUT_LINE('Courses unique to Student 1: ' || unique_courses.COUNT);
  FOR i IN 1..unique_courses.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(unique_courses(i));
  END LOOP;
END;

Output:

Courses unique to Student 1: 1
Science

EDB-SPL Procedure successfully completed

Conclusion

The introduction of MULTISET INTERSECT and MULTISET EXCEPT in EDB Postgres Advanced Server 16 is a great feature for database professionals. These operators not only enhance the power and flexibility of SQL in handling complex data sets but also streamline processes and potentially improve query performance. As databases continue to evolve, embracing these advanced features will be key in effectively managing and extracting value from complex data sets. Whether you are working in data analysis, reporting, or any field that requires sophisticated data manipulation, these new set operators in EDB Postgres Advanced Server 16 are invaluable tools in your database arsenal.

Exploring the New Horizons of MERGE in EDB Postgres Advanced Server 16

The database management landscape is continuously evolving, and with the introduction of PostgreSQL 15, a significant feature was introduced – the MERGE statement. This feature, inherited by EDB Postgres Advanced Server 15 from PostgreSQL 15, marked a turning point for database administrators and developers, especially those migrating from Oracle. However, the advancements in EPAS 16 are particularly interesting, especially for Oracle users familiar with the MERGE command, who will notice some differences in PostgreSQL’s implementation.

A Closer Look at the MERGE Statement

The MERGE statement is a structured command that significantly streamlines the way we manage data. It follows a specific syntax:

[ WITH with_query [, ...] ]
MERGE INTO target_table_name
USING data_source
ON join_condition
WHEN MATCHED THEN
  (UPDATE SET ...| DELETE | DO NOTHING)
WHEN NOT MATCHED THEN
  (INSERT (column_list) VALUES (...) | DO NOTHING)

This command is a game-changer for handling data in different scenarios. The USING clause allows you to specify the data source, which could range from a table to a subquery, or even a Common Table Expression (CTE). The ON clause is crucial as it defines the join condition, which determines how the rows from your data source and target table match up.

Handling Data with Precision

The real power of MERGE lies in its ability to handle matched and unmatched rows with precision. For rows that match, the UPDATE/DELETE clause modifies existing data, while the INSERT clause adds new rows to the target table when no match is found. This functionality encapsulates the essence of efficient data management.

The Advanced MERGE Feature in EDB Postgres Advanced Server 16

EDB Postgres Advanced Server 16 takes the MERGE command to the next level, offering enhanced flexibility that is a boon for DBAs and developers. Here are the key enhancements:

1. WHERE Clause for UPDATE/DELETE:

This addition allows for more precise data manipulation. For instance, consider the following example:

MERGE INTO target_students_1947 T
    USING (SELECT * FROM source_students_1947) S
    ON (T.std_id = S.std_id)
    WHEN MATCHED THEN
        UPDATE SET T.std_name = 'Mark', T.dob = S.dob WHERE T.std_id = 2
    WHEN NOT MATCHED THEN
        INSERT (std_id, std_name, dob) VALUES (S.std_id, S.std_name, S.dob);

Here, the WHERE condition with the UPDATE command under the WHEN MATCHED clause provides an extra layer of control and precision.

2. Combining Multiple Operations Under One Clause:

This feature allows for combining UPDATE/DELETE statements with a WHERE condition under a single WHEN MATCHED clause, as shown below:

MERGE INTO target_students_1947 T
    USING (SELECT * FROM source_students_1947) S
    ON (T.std_id = S.std_id)
    WHEN MATCHED THEN
        UPDATE SET T.std_name = 'Mark', T.dob = S.dob WHERE T.std_id = 2
        DELETE WHERE T.std_name = 'Mark'
    WHEN NOT MATCHED THEN
        INSERT (std_id, std_name, dob) VALUES (S.std_id, S.std_name, S.dob);

Advantages of Enhanced Flexibility

1. Efficient Data Management: With the ability to execute multiple statements under WHEN MATCHED condition, developers can manipulate data more efficiently. For example, imagine a scenario where a developer needs to update rows using source data and simultaneously ensure that the updates do not affect certain datasets. This flexibility is invaluable.
2. Simplified Migration from Oracle: For those migrating from Oracle to Postgres, these enhancements in EPAS 16 simplify the migration process, making it less daunting to adapt existing SQLs, procedures, and functions to the EPAS 16.

In summary, the advanced MERGE features in EPAS 16 represent a significant step forward in database management. They not only provide more control and flexibility but also ease the migration for those migrating from other database systems like Oracle. For developers and database administrators, these enhancements open up new possibilities for efficient and precise data handling, marking an exciting development in the world of database technology.