Contents

• pg_synonym
  • Why?
  • Usage
    • Create and drop
    • List
    • Use it like the table
    • SQL Server → PostgreSQL cheat sheet
  • What it keeps in sync
  • Permissions
  • Installation
    • Linux (Debian / Ubuntu)
    • Linux (Rocky / RHEL / Fedora, PGDG packages)
    • macOS (Homebrew)
    • Docker (any OS, including Windows)
    • Enable it in your database
  • Limitations
  • Performance
  • How it works
  • Running the tests
  • Contributing
  • License

pg_synonym

CREATE SYNONYM for PostgreSQL. Give a table or view a second name in another schema, and keep using it for reads and writes while the real table changes underneath.

🇧🇷 Leia em português

-- SQL Server:  CREATE SYNONYM vendas.cliente FOR erp.clientes
SELECT synonym.create('vendas.cliente', 'erp.clientes');

SELECT * FROM vendas.cliente;                       -- reads erp.clientes
INSERT INTO vendas.cliente (nome) VALUES ('Ana');   -- writes erp.clientes

Why?

SQL Server and Oracle have synonyms. Applications use them to hide where a table really lives, which schema it is in, or what it is called this year. PostgreSQL has no synonyms, so migrations usually fall back on one of two workarounds:

• A view. It works until someone runs ALTER TABLE on the real table. New columns do not show up, DROP COLUMN and ALTER COLUMN TYPE are refused (“other objects depend on it”), and DROP TABLE needs CASCADE.
• search_path tricks. These are global, fragile and invisible in the code.

pg_synonym gives you real synonyms. Each one is backed by an updatable view, and the extension keeps that view in step with its target automatically, whatever ALTER TABLE throws at it.

Usage

Create and drop

SELECT synonym.create('vendas.cliente', 'erp.clientes');
SELECT synonym.drop('vendas.cliente');
SELECT synonym.drop('vendas.cliente', if_exists => true);

DROP VIEW vendas.cliente, or DROP OWNED BY the synonym’s owner, also drops the synonym.

Names follow the usual PostgreSQL rules. Without a schema, the synonym goes into the first schema of search_path, and the target is looked up through search_path. Quoted names work: synonym.create('app."Pedidos"', 'erp."Old Orders"').

List

SELECT * FROM synonym.list;

    synonym     |    target     | owner |  status  |          created_at
----------------+---------------+-------+----------+-------------------------------
 vendas.cliente | erp.clientes  | ana   | ok       | 2026-10-19 15:47:28.03-03
 vendas.produto | erp.produtos  | ana   | pending  | 2026-10-19 15:48:02.11-03

status	meaning
ok	Working.
pending	The target does not exist yet. The synonym starts working as soon as it is created.
conflict	Another object already uses the synonym’s name. Rename or drop it.
out of sync	Should not happen. Run SELECT synonym.refresh();.

Use it like the table

SELECT ... FROM vendas.cliente JOIN ...;
INSERT INTO vendas.cliente (...) VALUES (...) RETURNING id;
INSERT ... ON CONFLICT (id) DO UPDATE ...;
UPDATE vendas.cliente SET ...;
DELETE FROM vendas.cliente WHERE ...;

The target’s defaults, identity columns, constraints, triggers and row-level security all apply. The planner sees straight through to the real table, so queries through a synonym are exactly as fast as queries on the table.

SQL Server → PostgreSQL cheat sheet

SQL Server	pg_synonym
CREATE SYNONYM s.x FOR s.y	SELECT synonym.create('s.x', 's.y')
DROP SYNONYM s.x	SELECT synonym.drop('s.x')
DROP SYNONYM IF EXISTS s.x	SELECT synonym.drop('s.x', if_exists => true)
SELECT * FROM sys.synonyms	SELECT * FROM synonym.list
CREATE SYNONYM x FOR otherdb.dbo.y	Not supported. Create a foreign table with postgres_fdw and point the synonym at it

What it keeps in sync

You do this to the target	The synonym
ADD COLUMN	shows the new column
RENAME COLUMN	uses the new name
ALTER COLUMN ... TYPE	uses the new type (a plain view would block this)
DROP COLUMN	loses the column (a plain view would block this)
RENAME TO, SET SCHEMA	keeps pointing at the same table, like a view would
DROP TABLE	stays defined and becomes pending (no CASCADE needed)
create it again	starts working again, with the new columns

You can also create a synonym before its target exists (late binding, as in SQL Server). It becomes pending and comes alive when the target is created.

Views that you build on top of a synonym survive harmless changes such as adding or renaming columns. Changes that need the synonym rebuilt (DROP COLUMN, ALTER COLUMN TYPE, DROP TABLE) are refused while such views exist, which is the same thing PostgreSQL does for any view. Use CASCADE where the command accepts it.

Permissions

A synonym never gives anyone more access than they already have to the target.

• Every synonym view is created with security_invoker = true. The person using the synonym needs the privileges on the target, and the target’s row-level security policies apply to them.
• To create a synonym you need CREATE privilege on the synonym’s schema, just as for creating a view, and USAGE on the target’s schema so you can name it. You do not need any privilege on the target table itself.
• Only the synonym’s owner, a member of the owner’s role, or a superuser can drop it.
• The table’s owner can still ALTER or DROP the table even when other users have synonyms on it.

Installing the extension requires a superuser, because it uses event triggers. Using it does not.

Installation

Requires PostgreSQL 15 or newer. There is nothing to add to shared_preload_libraries and no restart.

Linux (Debian / Ubuntu)

sudo apt install build-essential postgresql-server-dev-17   # use your PostgreSQL major version
git clone https://github.com/Mazuco/pg_synonym.git
cd pg_synonym
make
sudo make install

Linux (Rocky / RHEL / Fedora, PGDG packages)

sudo dnf install -y gcc make git redhat-rpm-config dnf-plugins-core
sudo dnf config-manager --set-enabled crb          # Rocky 9 (Rocky 8: --set-enabled powertools)
sudo dnf install -y postgresql17-devel
make PG_CONFIG=/usr/pgsql-17/bin/pg_config with_llvm=no
sudo make install PG_CONFIG=/usr/pgsql-17/bin/pg_config with_llvm=no

macOS (Homebrew)

brew install postgresql@17
make PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config
make install PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config

Docker (any OS, including Windows)

docker build -t pg_synonym .                    # PostgreSQL 17 by default
docker build --build-arg PG_MAJOR=18 -t pg_synonym:18 .
docker run -d --name pgsyn -e POSTGRES_PASSWORD=postgres -p 5432:5432 pg_synonym

Build error mentioning clang or llvm? Your PostgreSQL package was built with JIT support and tries to compile LLVM bitcode, which this extension does not need. Build with make with_llvm=no and install with sudo make with_llvm=no install.

Enable it in your database

CREATE EXTENSION pg_synonym;   -- as a superuser

The extension lives in its own schema, synonym.

Limitations

• Tables, views, materialized views, partitioned tables and foreign tables can be targets. Functions, procedures and sequences cannot (yet).
• No new syntax. PostgreSQL extensions cannot add SQL statements, so CREATE SYNONYM is a function call.
• No cross-database synonyms. Point the synonym at a postgres_fdw foreign table instead.
• A synonym of a synonym is not allowed. SQL Server does not allow it either.
• Things views cannot do do not work through a synonym either: TRUNCATE, COPY synonym TO (use COPY (SELECT * FROM synonym) TO), and DDL such as ALTER TABLE synonym. MERGE into a synonym needs PostgreSQL 17 or newer.
• Renamed targets are followed. A synonym keeps pointing at its table after RENAME or SET SCHEMA, as a view would. SQL Server binds by name instead.
• Manage synonyms only through these functions. Renaming a synonym’s view, its schema, or turning off security_invoker by hand breaks the link. When a synonym is rebuilt, GRANTs you added to it by hand are reset.
• DROP EXTENSION pg_synonym turns existing synonyms into ordinary views and leaves the synonym schema behind (DROP SCHEMA synonym removes it).

Performance

• Queries: no overhead. A synonym is a simple view that the planner removes.
• DDL: the event triggers add work to CREATE, ALTER and DROP of tables and views. Measured on PostgreSQL 16: about 0.4 ms per DDL with no synonyms, and about 11 ms per DDL with 500 synonyms.

How it works

A synonym is a row in synonym.catalog plus a view CREATE VIEW ... WITH (security_invoker = true) AS SELECT * FROM target. Event triggers keep them together:

1. Before DROP TABLE, DROP COLUMN or ALTER COLUMN TYPE on a target, a small C function reads the command, finds the synonyms involved and drops their views, so PostgreSQL does not refuse the command.
2. After any DDL on tables, views and schemas, a PL/pgSQL function compares each synonym with its target and creates, extends, renames or rebuilds the view as needed.
3. When someone drops a synonym’s view directly (DROP VIEW, DROP OWNED), the synonym is forgotten.

If the command fails, the whole transaction rolls back, including step 1. Everything except about 200 lines of commented C is plain PL/pgSQL in pg_synonym--1.0.sql. A line-by-line walkthrough in Portuguese is in docs/COMO_FUNCIONA.md.

Running the tests

make
sudo make install
make installcheck          # needs a running server and a superuser connection

Contributing

Issues and pull requests are welcome. Please add a regression test in test/sql/ and its expected output in test/expected/ for any change in behaviour.

License

Released under the PostgreSQL License.