Contents
You’ve just finished migrating an Oracle package to PostgreSQL. It was the usual mess: datatype research, NULL-handling surprises, schema naming decisions, verification against the real target database. You found answers, documented them locally, moved on.
Some days later, another package arrives. Same complexity. Same questions. You find yourself re-reading Oracle documentation, re-discovering that DATE in Oracle carries time but PG’s DATE does not, re-learning the custom date-string handling your company uses, re-verifying against schema dumps. The work is familiar. The decisions are the same. But you’re starting from scratch.
That’s the gap a skill closes.
A skill is a focused guide that embeds company-specific knowledge: the mappings, conventions, gotchas, verification recipes. It prevents the next person (or you, six months later) from re-inventing the decisions. The first migration builds the skill. Every subsequent migration follows it. The payoff compounds.
What Makes Something a Skill
A skill is three things working together:
Documentation that names the domain. Not generic migration advice, but your company’s specific guidance. The datatype bindings you settled on, the schema naming you enforce, the semantic bridges that trip up migrations, how to verify the port against the real target database.
Scope and triggering. A skill knows when to activate. It surfaces when a developer encounters the exact situation it was built for. The database migration skill triggers when someone says "I’m porting an Oracle package to PostgreSQL" or "I need to verify this port against the schema." Tight scope means high signal.
Allowed tools and constraints. A skill declares what it can do. The database migration skill gets Read, Write, Edit, and Bash for docker commands. It doesn’t get free network access or write permission everywhere. Constraint creates focus.
A skill lives in version control with your code. That knowledge stops scattering across Slack threads or lodging in whoever solved the problem last. It improves with use: each migration surfaces a gotcha to document, a detail to refine, a rule to sharpen.
Design: Identify What to Embed
Building a skill starts by separating what you invented from what you discovered. Your first migration taught you company-specific decisions: the schema naming convention you settled on, the datatype binding rule, the customer-specific quirks that don’t fit the standard. This is the gold—it won’t be in public documentation or tutorials. But you also discovered universal knowledge: how the source and target databases differ, what edge cases the migration framework doesn’t handle, what data transformation rules apply across similar migrations. A developer could find these by reading docs, but they shouldn’t have to. The skill needs both layers. The invented knowledge is why it exists. The discovered knowledge is what makes it complete.
When designing your skill, organize around layers:
-
Universal mappings and gotchas: the foundation everyone needs
-
Company binding: your specific standards and naming conventions
-
Customer quirks: variations customers introduce that don’t fit the standard rules
-
Verification: how to test the port against the real schema, not an extract
This layering helps when a rule seems backwards. A developer might see the casting rule (::text for amounts) and wonder why you’re being paranoid. The answer: it’s in the "customer quirks" layer because customers store amounts in different types. That context saves time and prevents the rule being quietly dropped.
Real Example: A Database Migration Skill
Here’s what a complete skill looks like, concrete and grounded.
The Datatype Binding
The skill starts with a mapping table. Universal first:
| Oracle | PostgreSQL | Note |
|---|---|---|
|
|
directly compatible |
|
|
depending on magnitude |
|
|
identical |
|
|
Oracle |
|
|
no length limit in PG |
|
|
compatible |
Then the company binding. For a database migration skill, this captures your company’s specific rules:
You MUST resolve bare NUMBER by value usage: integer-valued (ids, seqs, flags, enum codes, counters) becomes bigint to match your standard integer signatures; decimal becomes numeric.
VARCHAR2(n) columns holding date-strings in 'YYYYMMDD' format MUST stay varchar(n); do NOT convert to a date type.
A developer sees a bare NUMBER or a VARCHAR2 holding dates and doesn’t guess.
Then customer quirks. Different customers may store the same data differently (e.g. amounts in numeric vs. text), so the skill documents defensive patterns:
For columns whose type may differ per customer, you MUST cast through ::text before parsing so the routine works regardless of the source type: NULLIF(btrim(amount_field::text), '')::numeric::bigint.
Schema Naming Convention
PostgreSQL has no packages, so the skill resolves that with a deterministic rule. No debate about where code goes, no "where should I put this function?" ambiguity that wastes time and creates review friction:
You MUST resolve every package member into a standalone routine named <schema>.<package>_<member> (e.g. migration.payments_main, migration.payments_calculate_totals).
Semantic Bridges
Here’s where the skill captures gotchas that will burn you if you miss them:
NULL in string concatenation. Oracle treats NULL in || as ''; PG yields NULL and poisons the whole concatenation. COALESCE(x,'') every interpolated value when building audit/concat/protocol text.
SELECT INTO does NOT raise NO_DATA_FOUND in PG. Use an explicit IF NOT FOUND THEN …. Conversely, Oracle code that checked cur%NOTFOUND right after OPEN (before the first FETCH) is dead code: port it as a real NOT FOUND guard after the fetch, not a literal translation.
A developer who has spent an afternoon debugging why a log message got NULL instead of the concatenated value recognizes that rule instantly — it’s institutional memory, already written down.
Verification Recipe
The skill doesn’t stop at conversion rules. This is the difference between "I ported it" and "I verified it works," so it includes how to verify:
The source of truth for the target schema is a full schema-only pg_dump of the target DB when provided; verify every column and signature against it. A reduced schema extract is only a partial fallback and can pass at load time while still missing source-schema columns.
First Migration vs. Second
The first migration takes time: research, documentation, debugging, verification. By the second, the decisions are already made. You scan the skill, follow the patterns, verify the result and the developer after you never rediscovers the NULL concatenation bug or the type trap, because it’s already on the page.
Building skills gets easier with practice, too. After each migration, a wrap-up surfaces what worked and what surprised you, and a dedicated skill reviews that work and proposes changes to the guide: a gotcha to add, an edge case, a rule to sharpen. Apply the ones that matter and the guide improves every time it’s used, until documenting what you know is simply how your team works. The knowledge stops living in whoever solved the problem last and starts living in version control, where the next person can find it.
Comments
Nothing yet. Say the first thing.
Sign in to join the conversation.