Reviewing Oracle To Postgres Migration — Agent Skill by GitHub

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

Download the skill as a ZIP:
https://autogineer.com/agent-skills/github/reviewing-oracle-to-postgres-migration/download

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

Planning — Before starting migration work on a procedure, trigger, query, or refcursor client. Identify which reference insights apply so risks are addressed up front.
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

ATTRIBUTION.mdAdded by AutoGineer913 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md3.1 KiB
references/REFERENCE.md2.8 KiB
references/empty-strings-handling.md2.0 KiB
references/no-data-found-exceptions.md2.6 KiB
references/oracle-nvl-decode-functions.md2.4 KiB
references/oracle-parentheses-from-clause.md4.8 KiB
references/oracle-rownum-pagination.md2.1 KiB
references/oracle-sysdate-sequences-dual.md3.2 KiB
references/oracle-to-postgres-sorting.md2.7 KiB
references/oracle-to-postgres-timestamp-timezone.md8.4 KiB
references/oracle-to-postgres-to-char-numeric.md4.2 KiB
references/oracle-to-postgres-type-coercion.md4.9 KiB
references/postgres-concurrent-transactions.md9.4 KiB
references/postgres-materialized-view-refresh.md1.4 KiB
references/postgres-refcursor-handling.md5.7 KiB
references/postgres-union-all-planner.md1.6 KiB

references/empty-strings-handling.md

Oracle to PostgreSQL: Empty String Handling Differences

Problem

Oracle automatically converts empty strings ('') to NULL in VARCHAR2 columns. PostgreSQL preserves empty strings as distinct from NULL. This difference can cause application logic errors and test failures during migration.

Behavior Comparison

Oracle:

Empty string ('') is always treated as NULL in VARCHAR2 columns
WHERE column = '' never matches rows; use WHERE column IS NULL
Cannot distinguish between explicit empty string and NULL

PostgreSQL:

Empty string ('') and NULL are distinct values
WHERE column = '' matches empty strings
WHERE column IS NULL matches NULL values

Code Example

-- Oracle behavior
INSERT INTO table (varchar_column) VALUES ('');
SELECT * FROM table WHERE varchar_column IS NULL;  -- Returns the row

-- PostgreSQL behavior  
INSERT INTO table (varchar_column) VALUES ('');
SELECT * FROM table WHERE varchar_column IS NULL;  -- Returns nothing
SELECT * FROM table WHERE varchar_column = '';     -- Returns the row

Migration Actions

1. Stored Procedures

Update logic that assumes empty strings convert to NULL:

-- Preserve Oracle behavior (convert empty to NULL):
column = NULLIF(param, '')

-- Or accept PostgreSQL behavior (preserve empty string):
column = param

2. Application Code

Review code that checks for NULL and ensure it handles empty strings appropriately:

// Before (Oracle-specific)
if (value == null) { }

// After (PostgreSQL-compatible)
if (string.IsNullOrEmpty(value)) { }

3. Tests

Update assertions to be compatible with both behaviors:

// Migration-compatible test pattern
var value = reader.IsDBNull(columnIndex) ? null : reader.GetString(columnIndex);
Assert.IsTrue(string.IsNullOrEmpty(value));

4. Data Migration

Decide whether to:

Convert existing NULL values to empty strings
Convert empty strings to NULL using NULLIF(column, '')
Leave values as-is and update application logic

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.

Implement Spec
Matt Pocock· Community
A workflow for implementing technical specifications and tickets using concurrent subagents.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository317.5k
MCP Builder
AnthropicOfficial
A comprehensive guide for building high-quality Model Context Protocol (MCP) servers to integrate external services with LLMs.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
Apache-2.0Low riskHas scriptsGitHub stars of the source repository188.6k
Schema
Corey Haines· Community
Expert guidance for implementing and optimizing JSON-LD schema markup to improve search engine content understanding.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository59k
Postgresql Table Design
Seth Hobson· Community
A comprehensive guide for designing, reviewing, and optimizing PostgreSQL database schemas.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository42.3k
SQL Optimization Patterns
Seth Hobson· Community
A comprehensive guide to optimizing SQL database performance through indexing, query plan analysis, and efficient coding patterns.
Software developmentClaude CodeClaude appsOpenAI Codex+14 more
MITGitHub stars of the source repository42.3k
API Scaffolding Fastapi Pro
Seth Hobson· Community
An expert FastAPI developer focused on high-performance, async-first API architecture and production-ready microservices.
Software developmentClaude Code
MITGitHub stars of the source repository42.3k

Download ZIP

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