How to move tables from one schema to another in PostgreSQL

In this article, we will see how to move tables from one schema to another in PostgreSQL. We can move the tables from one schema to another by using alter table command. Schemas are database objects. Same schema can be exists in different. But, tables of schema are different.

Syntax:

ALTER TABLE table_name
SET SCHEMA schema_name;

Examples to move tables from one schema to another in PostgreSQL:

1. Verify the list of schemas in database ‘r2schools’. \dn is meta command used get list of schemas in the current database of PostgreSQL.

\dn

Or run the below queries to all schemas of PostgreSQL Cluster.

select nspname
from pg_catalog.pg_namespace;

select schema_name
from information_schema.schemata;

2. Lets verify current schema.

show search_path;

3. Get the list of tables of current schema.

\dt

 List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | students | table | postgres
 public | test     | table | tom

4. Now move table ‘test to sales schema.

ALTER TABLE test SET SCHEMA sales;

5. Now verify list of tables in current schema.

\dt

 List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | students | table | postgres

Which means table successfully moved from public schema.

Switch to sales schema and verify tables exists or not.

So, we have successfully movd table form one schema to another.