Migrating Oracle Synonyms: What PostgreSQL Offers Instead

The Object PostgreSQL Doesn't Have

Synonyms are one of the few Oracle features with no direct PostgreSQL equivalent — there is no CREATE SYNONYM. That sounds like a problem until you realise what synonyms actually do: they're an indirection layer that lets code reference an object by a stable name without caring which schema it lives in. PostgreSQL solves the same problem a different way — mostly with search_path, and occasionally with a view or a wrapper function. Once you map each synonym to its intent, almost all of them simply disappear.

This guide covers the intent-to-replacement map, the search_path mechanic that does most of the work, what to do about PUBLIC synonyms, and the one case that needs a real object.

Replace by Intent, Not by Syntax

There is no mechanical CREATE SYNONYM x FOR y → one-liner. Instead, ask what each synonym is for and pick the matching PostgreSQL construct:

search_path Does Most of the Work

The majority of Oracle synonyms exist so that HR's code can say EMPLOYEES instead of HR.EMPLOYEES, or so a reporting user can reach objects across schemas without qualifying every name. PostgreSQL's search_path is exactly that: an ordered list of schemas the planner checks when a name is unqualified.

-- Oracle: a private synonym so APP_USER can say EMPLOYEES, not HR.EMPLOYEES
CREATE SYNONYM app_user.employees FOR hr.employees;

-- PostgreSQL: put hr on the user's search_path instead — no object needed
ALTER ROLE app_user SET search_path = app_user, hr, public;

-- ...or per-session / per-database:
SET search_path = app_user, hr, public;            -- session
ALTER DATABASE appdb SET search_path = hr, public; -- database default

With hr on the path, every unqualified employees resolves just as the synonym made it. One ALTER ROLE typically retires dozens of private synonyms at once.

When You Need a Real Alias: Views and Wrappers

Sometimes the synonym genuinely renames an object — the code says CUSTOMERS but the table is CRM.CUSTOMER_MASTER. search_path can't rename, so create a thin object:

-- Alias a table/view under a different name → a view
CREATE VIEW sales.customers AS SELECT * FROM crm.customer_master;
-- (a simple SELECT * view is updatable: INSERT/UPDATE/DELETE pass through)

-- Alias a procedure/function → a thin wrapper
CREATE FUNCTION sales.recalc(p_id int) RETURNS void AS $$
BEGIN
  PERFORM crm.recalculate_customer(p_id);
END;
$$ LANGUAGE plpgsql;

PUBLIC Synonyms → A Shared Schema on the Path

Oracle PUBLIC synonyms make an object visible to every user unqualified — a common pattern for shared lookup tables and utility packages. The clean PostgreSQL equivalent is a shared schema added to the database's default search_path, so every role inherits it:

-- Oracle
CREATE PUBLIC SYNONYM country_codes FOR ref.country_codes;

-- PostgreSQL: one shared schema, on everyone's path by default
CREATE SCHEMA shared;
-- (move/own shared objects here: ref.country_codes -> shared.country_codes)
ALTER DATABASE appdb SET search_path = "$user", public, shared;
GRANT USAGE ON SCHEMA shared TO PUBLIC;

Two Things to Watch

• Resolution order matters. If the same unqualified name exists in two schemas on the path, the first wins. Oracle synonyms were explicit; search_path is positional — order it deliberately and watch for shadowing.
• Don't recreate indirection you don't need. The instinct is to build a view for every synonym. Most are pure schema-qualification convenience that search_path handles for free — reserve views/wrappers for genuine renames.

How DBMigrateAIPro Handles Synonyms

During assessment, the tool inventories every private and PUBLIC synonym and classifies it by intent: schema-qualification convenience (→ a search_path entry), a genuine rename (→ a generated view or wrapper), or a DB-link target (→ flagged for FDW). The conversion emits the ALTER ROLE/DATABASE … SET search_path statements and the handful of views that are actually needed — so the "no CREATE SYNONYM" surprise is a solved checklist item, not a cutover-day scramble.