RENAME TABLE

Function

Modify the table name, including modifying the table schema, renaming the table, and deleting the table permissions.

RENAME TABLE old_table to new_table;

Precautions

• This section contains only the syntax added by dolphin, the original MogDB syntax has not been removed or modified.
• When there are multiple commands to modify table names under the rename table statement, the syntax sorts the table names to be modified, then locks them in order, and then modifies the table names in left-to-right order. When rename table a to b, b to c, and table b does not exist in the middle, it is skipped without locking.
• For the table does not exist, and the target table and the existence of the table conflict, then the corresponding error message is reported. When a table has a synonym, the original table cannot have a synonym dependency, and the target table cannot have a synonym with the same name.
• When modifying the table name and schema of the target table, it will determine whether the current user has privileges on the table.

RENAME TABLE Syntax

• Modify the table definition.

    RENAME TABLE old_schema.table_name TO new_schema.new_table_name [, old_schema.table_name TO new_schema.new_table_name ...];

Parameter Description

RENAME TABLE can rename one or more tables at the same time. But you must have ALTER and DROP privileges on the old table, and CREATE and INSERT privileges on the new table. You must also have permissions on old_schema and new_schema.

• Modify table name

  RENAME TABLE old_table to new_table;

• When the old table and the new table are under the same schema, RENAME TABLE is equivalent to the following syntax

  ALTER TABLE old_table RENAME TO new_table;

• RENAME TABLE supports modification of multiple table names in a single sql syntax and its execution order is from left to right.

  RENAME TABLE A TO B, B TO C, C TO A;

• RENAME TABLE contains table locking operations, and the order of table locking is based on the old table's schema.table to sort, and then sequentially lock the sorted table. Cross-schema modification of table names in MogDB is equivalent to cross-library modification of table names in MySQL.

  RENAME TABLE old_schema.old_table TO new_schema.new_table;

• The old_table cannot be a synonym and cannot store synonym dependencies. new_table cannot be a synonym.

• RENAME TABLE modifies the table name and also modifies the data types in the system table pg_type that have the same name as old_table. and dependencies in the system table pg_depend.

• Temporary tables and global temporary tables are not supported.

• Cross-schema modification of table names for views is not supported. Only modification of table names under the same schema is supported.

• RENAME TABLE After changing the table name, new_table does not have the privileges of old_table. You must reassign privileges to the new_table as a superuser.

• REANME TABLE syntax, old_table If old_table has triggers, old_table cannot change the table name across schemas.

• old_table and new_table cannot be the same before and after.

• RENAME TABLE syntax If old_table does not specify a schema, old_table is traversed from the search_path until it is found, or an error is reported that old_table does not exist. If new_table does not specify a schema, new_table is in the same schema as old_table.

Helpful Links

ALTER TABLE