PostgreSQL Rename Table using Simple Steps

February 4, 2021

A very simple way to rename table in PostgreSQL is using alter table statement. While changing table name you would be thinking about what will happen to indexes and other constraints on the table. Obviously these questions are obvious so lets explore more about it.

Overview of PostgreSQL Rename Table Statement

In my previous post you read about PostgreSQL Create Database statement to create new database using command line. In this post we will discuss in detail about renaming table and its impact.

PostgreSQL has rename table statement which is used with rename clause to change the name of an existing table.

Syntax: 

ALTER TABE table_name
RENAME to new_table_name;

In the above statement, first you specify the existing table name which you want to rename and secondly, specify new table name.

So lets see an example by creating simple table and renaming it.

CREATE TABLE test(
 name VARCHAR NOT NULL,
 city VARCHAR(20) NOT NULL,
 id serial PRIMARY KEY
 );

Insert data into table

postgres=# insert into test values('Troy','Delhi',111);
postgres=# select * from test;
  name | city  | id
 ------+-------+-----
  Troy | Delhi | 111
 (1 row)

Lets Create an Index on a Table before Renaming it.

postgres=# create index idx_name on test(name);
 CREATE INDEX

postgres=# select tablename,indexname from pg_indexes where tablename='test';
  tablename | indexname
 -----------+-----------
  test      | test_pkey
  test      | idx_name
 (2 rows)

Now, Lets rename existing table and see what will happen to indexes and data to old and new table.

postgres=# alter table test rename to dev;
 ALTER TABLE

postgres=# select * from test;
 ERROR:  relation "test" does not exist
 LINE 1: select * from test;

postgres=# select tablename,indexname from pg_indexes where tablename='test';
  tablename | indexname
 -----------+-----------
 (0 rows)

As we can see above, older table “test” doesn’t exist and there is no index on older table. All the data and indexes has been migrated to new table.

Now check New table data and indexes.

postgres=# select * from dev;
  name | city  | id
 ------+-------+-----
  Troy | Delhi | 111
 (1 row)

postgres=# select tablename,indexname from pg_indexes where tablename='dev';
  tablename | indexname
 -----------+-----------
  dev       | test_pkey
  dev       | idx_name
 (2 rows)

As we can see above, our old table has been renamed to new one and all the existing rows and indexes are automatically created on new table.

Important Point:

When you rename a table, postgresql will automatically update its dependent objects like indexes, constraints and views.

Moving table into different Schema and renaming it:

Currently, our table is into public schema. Lets create one schema with name “myschema” and move table into myschema.

postgres=# \d
              List of relations
  Schema |    Name     |   Type   |  Owner
 --------+-------------+----------+----------
  public | dev         | table    | postgres
  public | test_id_seq | sequence | postgres

postgres=# CREATE SCHEMA myschema;
 CREATE SCHEMA

postgres=# select * from pg_tables where schemaname='myschema';
  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
 ------------+-----------+------------+------------+------------+----------+-------------+-------------
 (0 rows)

Moving table into different schema:
postgres=# alter table public.dev SET SCHEMA myschema;
 ALTER TABLE
 postgres=#
 postgres=# select * from pg_tables where schemaname='myschema';
  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
 ------------+-----------+------------+------------+------------+----------+-------------+-------------
  myschema   | dev       | postgres   |            | t          | f        | f           | f
 (1 row)

As we can see our table is moved to schema called “myschema”

Now rename table “dev” which is into myschema from public schema.

postgres=# ALTER TABLE myschema.dev RENAME TO prod;
 ALTER TABLE
postgres=#  select * from pg_tables where schemaname='myschema';
  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
 ------------+-----------+------------+------------+------------+----------+-------------+-------------
  myschema   | prod      | postgres   |            | t          | f        | f           | f
 (1 row)

postgres=# select * from pg_tables where schemaname='public' and tablename='prod';
  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
 ------------+-----------+------------+------------+------------+----------+-------------+-------------
 (0 rows)
No table with name prod into public schema because it is moved to myschema.

In the above example, we created new schema with name “myschema” and moved table from public schema to myschema and renamed table to a different schema.

Must Read: