How to Rename a Table Inside a Schema in PostgreSQL

In PostgreSQL, we often tend to CREATE tables and use them to store data. When we CREATE a table such as follows:

create table cat (
    ID int,
    NAME text
)

We already assigned a NAME to our table when we created it. In the example above, we gave the name CAT to our table, and once it is fixed, we probably have to use other means to RENAME our table once it has been made.

The syntax for CREATING a table goes as follows:

CREATE TABLE [Table_name] (
     [variable_names] [variable_types]
)

What is a SCHEMA? A SCHEMA contains database objects such as tables, views, indexes, and possibly everything that can be contained inside your DBMS.

Now let’s go ahead and learn how we can RENAME our table inside our SCHEMA once it has been made.

the Basic Renaming Syntax in PostgreSQL

To rename a table already created in PostgreSQL, we can use the ALTER statement that tends to make changes or modify some object already made inside the PostgreSQL schema. An ALTER statement that would tend to RENAME a table uses syntax.

ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name

Let’s take the example of the CAT table that we use several times in our tutorials. A SELECT operation on our database would return something as follows.

Select * from information_schema.tables
where table_name = 'cat'

Output:

"postgres'        "public"    "cat"        "BASE TABLE"

And then, we can perform the RENAMING operation on this table as follows.

alter table CAT rename to KITTEN

And now, when we look up CAT in our database, it would return an empty result. However, looking up KITTEN would work perfectly with the same values as the CAT table had.

"postgres"	"public"	"kitten"	"BASE TABLE"

Hence, our basic RENAME with the ALTER function works perfectly. You can perform this same query in PSQL by using something.

ALTER TABLE cat rename to Kitten;

And it will return output as follows on success.

Output:

Errors While RENAMING Specific SCHEMA Tables in PostgreSQL

The syntax error is one of the most common errors while performing a RENAME operation on a table made inside a SCHEMA different from the one present currently.

For example, if you tend to make a SCHEMA as follows:

CREATE SCHEMA tester;

And then make a table inside it.

CREATE TABLE tester.dog (
   id INT,
   name TEXT
)

The table now resides inside the specified SCHEMA. However, calling the RENAME operation the following way will return an error.

ALTER TABLE tester.dog RENAME TO tester.doggy

Output:

ERROR:  syntax error at or near "."
LINE 1: ALTER TABLE tester.dog RENAME TO tester.doggy

Why? If you look at the RENAME text, it says something as follows.

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name

The new_column_name has to be a valid STRING. So you cannot use other characters such as . and ,, which may invalidate a STRING.

Hence if you go ahead and do something as follows.

alter table tester.dog rename to doggy;

It will work perfectly fine. So now, if we look at the DOMAIN.DOG table, it would have been RENAMED to DOMAIN.DOGGY.

Another way to accomplish this is to use a workaround that tends to RENAME the table into something we see above. Because we are currently working with a SCHEMA, we may encounter errors while using the . notation to specify a table inside a SCHEMA.

Now we will go ahead and learn about SEARCH PATHS.

SEARCH PATHS for RENAMING SCHEMA TABLES in PostgreSQL

SEARCH PATHS are defined globally as tending to change the reference of the SCHEMA currently being referenced to the one defined in the SEARCH PATH syntax. It uses syntax as follows.

search_path (string)

And is defined under the CLIENT CONNECTION DEFAULTS. Run the following query to RENAME the TABLE.

SET search_path to tester;
alter table doggy rename to dogie

It SETS the search_path to the DOMAIN schema, and then when we call the table DOGI, it automatically knows that the table is referenced from the DOMAIN schema.

Hence this one-line query is much better to reference tables inside a SCHEMA rather than calling a DOMAIN.TABLE_NAME.

SEARCH_PATH can also have multiple SCHEMA names separated by commas, and when we call a TABLE or OBJECT, it is checked from each of these defined SCHEMAS.

You can also add the IF EXISTS clause to check if the TABLE or OBJECT referenced exists and then go ahead and run it.

We hope you learned how to RENAME tables in PostgreSQL with different options and scenarios. Please note that different variations of the RENAME statement may work in many cases.

When working in a database, know which SCHEMA is referenced, which OBJECT is called and where it is required to prevent and avoid errors. We hope that our solutions can help you understand the different ways a table can be RENAMED.