AutoGineer
Generated banner for Reviewing Oracle To Postgres Migration
Generated logo for Reviewing Oracle To Postgres Migration

Reviewing Oracle To Postgres Migration

GitHubOfficialFirst committed by PrimedPaulDistributed by GitHub

  • Skill
  • Workflow
  • Software development
  • MIT
  • No issues found
Download ZIP

From the author

Identifies Oracle-to-PostgreSQL migration risks by cross-referencing code against known behavioral differences (empty strings, refcursors, type coercion, sorting/collations, UNION ALL planner risks, materialized-view refresh requirements, timestamps, concurrent transactions, etc.). Use when planning a database migration, reviewing migration artifacts, or validating that integration tests cover Oracle/PostgreSQL differences.
— the description by PrimedPaul

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a systematic approach to managing database migrations from Oracle to PostgreSQL. It centers on a reference-based workflow that helps developers identify behavioral differences—such as empty string handling, type coercion, and pagination logic—during the planning phase and verify that these risks are addressed during the validation phase. By cross-referencing migration artifacts against known technical discrepancies, it ensures that application logic and database schemas are correctly adapted for the PostgreSQL environment.

What it does

  • Provides a risk assessment workflow for planning database migrations.
  • Offers a validation workflow to confirm migration work meets PostgreSQL requirements.
  • Maintains a library of known behavioral differences between Oracle and PostgreSQL.
  • Guides users through documenting risks and recommended fix patterns.
  • Ensures integration tests cover critical Oracle-to-PostgreSQL behavioral gaps.
  • Supports decision-making for handling specific database object migrations.

When to use it

  • When planning the migration of database objects like procedures, triggers, or views.
  • When reviewing completed migration artifacts to ensure all behavioral risks are mitigated.
  • When validating that integration tests adequately cover Oracle-to-PostgreSQL differences.
  • When you need a structured checklist to gate the success of a database migration task.

Advantages

  • Reduces the risk of runtime errors caused by subtle database behavioral differences.
  • Standardizes the migration process across different database objects.
  • Provides clear, actionable guidance for common migration pitfalls.
  • Facilitates thorough testing by highlighting specific scenarios that require validation.

Usage examples

“I am planning to migrate a set of Oracle stored procedures to PostgreSQL. How should I start?”
The agent will guide you through the Risk Assessment workflow, helping you list affected objects and screen them against the reference index to document necessary changes.
“I have finished migrating a query that uses ROWNUM. How do I validate this?”
The agent will initiate the Validation workflow, prompting you to map the artifact, check it against the ROWNUM-specific reference, and verify that your integration tests cover the new LIMIT/OFFSET logic.

Install

  1. Download the skill as a ZIP:

    https://autogineer.com/agent-skills/github/reviewing-oracle-to-postgres-migration/download

  2. Unzip it into the cross-client skills folder of your project:

    mkdir -p .agents/skills && unzip reviewing-oracle-to-postgres-migration.zip -d .agents/skills

Documentation by PrimedPaul

Oracle-to-PostgreSQL Database Migration

Surfaces migration risks and validates migration work against known Oracle/PostgreSQL behavioral differences documented in the references/ folder.

When to use

  1. Planning — Before starting migration work on a procedure, trigger, query, or refcursor client. Identify which reference insights apply so risks are addressed up front.

  2. Validating — After migration work is done, confirm every applicable insight was addressed and integration tests cover the new PostgreSQL semantics.

Workflow

Determine the task type:

Planning a migration? Follow the risk assessment workflow. Validating completed work? Follow the validation workflow.

Risk assessment workflow (planning)
Risk Assessment:
- [ ] Step 1: Identify the migration scope
- [ ] Step 2: Screen each insight for applicability
- [ ] Step 3: Document risks and recommended actions

Step 1: Identify the migration scope

List the affected database objects (procedures, triggers, queries, views) and the application code that calls them.

Step 2: Screen each insight for applicability

Review the reference index in references/REFERENCE.md. For each entry, determine whether the migration scope contains patterns affected by that insight. Read the full reference file only when the insight is potentially relevant.

Step 3: Document risks and recommended actions

For each applicable insight, note the specific risk and the recommended fix pattern from the reference file. Flag any insight that requires a design decision (e.g., whether to preserve Oracle empty-string-as-NULL semantics or adopt PostgreSQL behavior).

Validation workflow (post-migration)
Validation:
- [ ] Step 1: Map the migration artifact
- [ ] Step 2: Cross-check applicable insights
- [ ] Step 3: Verify integration test coverage
- [ ] Step 4: Gate the result

Step 1: Map the migration artifact

Identify the migrated object and summarize the change set.

Step 2: Cross-check applicable insights

For each reference in references/REFERENCE.md, confirm the behavior or test requirement is acknowledged and addressed in the migration work.

Step 3: Verify integration test coverage

Confirm tests exercise both the happy path and the failure scenarios highlighted in applicable insights (exceptions, sorting, UNION ALL behavior/performance risks, refcursor consumption, concurrent transactions, timestamps, materialized-view freshness, etc.).

Step 4: Gate the result

Return a checklist asserting each applicable insight was addressed, migration scripts run, and integration tests pass.

Files

references/oracle-nvl-decode-functions.md

Oracle to PostgreSQL: NVL, DECODE, and Null-Handling Functions

Problem

Oracle provides several functions — NVL, NVL2, DECODE — that have no direct equivalents in standard SQL but are commonly used in Oracle stored procedures and inline SQL. PostgreSQL uses standard SQL alternatives: COALESCE, NULLIF, and CASE expressions.

Behavior Comparison

NVL

Oracle: NVL(expr, replacement) — returns replacement if expr is NULL, otherwise expr.

PostgreSQL: Use COALESCE(expr, replacement) — semantically identical for two arguments.

-- Oracle
NVL(column_name, 'default')

-- PostgreSQL
COALESCE(column_name, 'default')
NVL2

Oracle: NVL2(expr, not_null_val, null_val) — returns not_null_val if expr IS NOT NULL, null_val if NULL.

PostgreSQL: No direct equivalent — use CASE:

-- Oracle
NVL2(column_name, 'has value', 'no value')

-- PostgreSQL
CASE WHEN column_name IS NOT NULL THEN 'has value' ELSE 'no value' END
DECODE

Oracle: DECODE(expr, search1, result1, search2, result2, ..., default) — equality-based switch.

PostgreSQL: No DECODE function — use CASE WHEN:

-- Oracle
DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown')

-- PostgreSQL
CASE status
  WHEN 1 THEN 'Active'
  WHEN 2 THEN 'Inactive'
  ELSE 'Unknown'
END

Note: DECODE in Oracle treats two NULL values as equal (unlike =). If any search value is NULL, use IS NULL in the CASE equivalent:

-- Oracle: DECODE treats NULL = NULL
DECODE(col, NULL, 'empty', col)

-- PostgreSQL
CASE WHEN col IS NULL THEN 'empty' ELSE col END

Migration Actions

1. Stored Procedures

Apply the direct substitutions above. Pay special attention to:

  • NVL on numeric expressions — COALESCE is type-sensitive in PostgreSQL; ensure both arguments are the same type or cast explicitly.

  • DECODE with NULL search values — replace with IS NULL guard in the CASE expression.

2. Application Code (inline SQL strings)

Search C# string literals and query builders for NVL(, NVL2(, and DECODE(. Apply the same substitutions.

3. Tests

Write test cases that exercise NULL inputs specifically — the Oracle → PostgreSQL translation of NVL/COALESCE is straightforward, but edge cases around NULL equality in DECODE → CASE are a common source of silent behavioral differences.

Requirements and compatibility

Written for: GitHub Copilot

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

First committed by
GitHub avatar of PrimedPaulPrimedPaul
Publisher
GitHub (Official)
Distributed by
GitHub
Source
github/awesome-copilot · skills/reviewing-oracle-to-postgres-migration · commit 797337f
Licence
MIT License (redistribution allowed)
  • LICENSE (added by AutoGineer from LICENSE)
  • ATTRIBUTION.md (written by AutoGineer)
Synced
Contributors
PrimedPaul

AutoGineer is not affiliated with or endorsed by PrimedPaul or GitHub.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download reviewing-oracle-to-postgres-migration.zip