Moving a Table to Another Schema

The billing team is preparing to extract its tables into a separate service and database. Step one is to gather invoices, invoice_lines and payments — currently mixed into the public schema with everything else — into a billing schema, so ownership, permissions and eventually replication can be managed as a unit. ALTER TABLE invoices SET SCHEMA billing is instant. It also immediately breaks every query that says public.invoices explicitly, and every query that says invoices from a role whose search_path does not include billing. This guide moves tables between schemas online, choosing the right bridge for how your code refers to tables, and handles the permission and dependency details that make moves fail. It belongs to Renaming and Splitting Tables Online.

Which Bridge Does Your Code Need? Decision tree. If application queries use unqualified table names, adding the new schema to the roles' search_path bridges the move. If queries are schema-qualified with public, create views in public with the old names pointing at the moved tables. If both occur, use both bridges. Which Bridge Does Your Code Need? Do queries use unqualified names (FROM invoices)? yes no Add billing to search_path before the move Views public.invoices → billing.invoices
How your code names tables decides the bridge — unqualified names need a search path, qualified names need views.

Symptom / Error Signatures

A move without a bridge fails like a rename:

ERROR:  relation "public.invoices" does not exist
ERROR:  relation "invoices" does not exist        -- unqualified, search_path lacks the new schema
ERROR:  permission denied for schema billing      -- role lacks USAGE on the new schema

The last one is easy to miss in testing with a superuser: tables keep their own grants when moved, but a role also needs USAGE on the schema that contains them.

Root Cause Analysis

ALTER TABLE ... SET SCHEMA changes only the table’s namespace in the catalog. It takes ACCESS EXCLUSIVE briefly, moves the table’s indexes, constraints and owned sequences along with it, and keeps the table’s privileges. Internal references — views, foreign keys, triggers — follow the table by OID and keep working. What changes is name resolution for queries written as text:

Query form After the move Bridge
FROM invoices, role search_path = public fails add billing to the role’s search_path
FROM invoices, search_path = "$user", public, billing works none needed
FROM public.invoices fails view public.invoices over billing.invoices
FROM billing.invoices (new code) works —

search_path changes affect only new sessions (or sessions that run SET search_path), so for pooled connections the change must be in place — and the pool recycled — before the move. Views are resolved at query time and take effect immediately, and simple views are automatically updatable, as described in renaming a table with an updatable view.

Moving a Table Group Between Schemas Five steps. Create the billing schema with owner and USAGE grants; extend search_path for application roles and recycle pools; move tables with SET SCHEMA in one transaction together with bridge views for qualified names; migrate code to billing-qualified names; remove bridges and trim search_path. Moving a Table Group Between Schemas STEP 1 Create schema owner + USAGE grants STEP 2 Extend search_path roles, recycle pools STEP 3 SET SCHEMA + views one transaction STEP 4 Update code billing.* names STEP 5 Remove bridges views, search_path
Bridges go in before or with the move, and come out only after code no longer needs them.

Immediate Mitigation

If a move already broke queries, restore the old name immediately with a view (for qualified queries) or by adding the schema to the search path (for unqualified ones):

-- PostgreSQL · migration role · restores public.invoices as an updatable view
SET lock_timeout = '3s';
CREATE VIEW public.invoices AS SELECT * FROM billing.invoices;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.invoices TO app;
GRANT USAGE ON SCHEMA billing TO app;
-- ROLLBACK PATH: DROP VIEW public.invoices;
-- PostgreSQL · superuser or role owner · affects new sessions of the role; recycle pools afterwards
ALTER ROLE app SET search_path = "$user", public, billing;

Permanent Fix / Long-Term Pattern

1. Prepare the schema and privileges.

-- PostgreSQL · superuser or database owner
CREATE SCHEMA IF NOT EXISTS billing AUTHORIZATION billing_owner;
GRANT USAGE ON SCHEMA billing TO app, reporting;
ALTER DEFAULT PRIVILEGES IN SCHEMA billing GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

2. Extend search_path for roles using unqualified names, then recycle connection pools so every session picks it up. Verify with SHOW search_path from an application session.

3. Move the table group in one transaction, with views for qualified names. Moving related tables together keeps foreign keys and joins within one move.

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE on each table
-- WARNING: include views for every table that code references as public.<table>.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE public.invoices      SET SCHEMA billing;
ALTER TABLE public.invoice_lines SET SCHEMA billing;
ALTER TABLE public.payments      SET SCHEMA billing;
CREATE VIEW public.invoices      AS SELECT * FROM billing.invoices;
CREATE VIEW public.invoice_lines AS SELECT * FROM billing.invoice_lines;
CREATE VIEW public.payments      AS SELECT * FROM billing.payments;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.invoices, public.invoice_lines, public.payments TO app;
COMMIT;
-- ROLLBACK PATH: drop the three views and SET SCHEMA public for the three tables in one transaction.

Note a subtlety: with both the view public.invoices and the table billing.invoices present, an unqualified invoices resolves to whichever schema comes first in search_path. With public first it resolves to the view, which is updatable and therefore works — but for clarity, move code to explicit billing. names.

4. Update code to qualified billing.* names service by service, and confirm through query statistics that nothing uses the public names any more, as in Renaming and Splitting Tables Online.

5. Remove the bridges — drop the views and remove billing from search_path if it is no longer needed. Once the schema is self-contained, extracting it to its own database becomes a replication task, discussed in dual-writing across two databases during a cutover.

Name Resolution During the Transition During the transition, three query forms coexist. Unqualified invoices resolves through search_path to the public view. Qualified public.invoices hits the view. Qualified billing.invoices hits the table directly. All three reach the same rows in billing.invoices. Name Resolution During the Transition FROM invoices unqualified, search_path FROM public.invoices old qualified name FROM billing.invoices new code public.invoices view updatable billing.invoices the table direct forwards
Three spellings, one table — the bridges exist so that every spelling in running code still resolves.

Two more dependencies deserve a check before the move. Functions and procedures written in SQL or PL/pgSQL store their bodies as text and resolve table names when they run, using the function’s own search_path setting if it has one; a function declared with SET search_path = public will stop finding the moved table. List such functions with SELECT proname, proconfig FROM pg_proc WHERE proconfig IS NOT NULL and update their settings or bodies. And logical replication publications or CDC connectors that list tables by qualified name need their table lists updated, or they will silently stop streaming the moved tables. Backup and restore tooling that selects tables by schema (for example pg_dump --schema=public for a partial dump) needs the same update, so that the moved tables are still captured by whatever job used to include them.

Verification Checklist

Frequently Asked Questions

Does SET SCHEMA copy the table? No. It changes the table’s namespace in the catalog. Data, indexes, constraints and owned sequences stay where they are and move with it logically.

Do foreign keys between moved and unmoved tables still work? Yes. Foreign keys reference tables by OID, not by name, so they are unaffected by the move.

Why do I need to recycle connection pools after changing search_path? ALTER ROLE ... SET search_path applies to new sessions. Pooled connections opened earlier keep the old value until they reconnect.

Can I move a table between databases the same way? No. Databases do not share catalogs, so moving between them means copying data and keeping it in sync — logical replication or CDC — and then cutting over, which is a separate procedure.

Related Articles