ApsaraDB RDS:What do I do if the "relation "xxx" already exists" error message is displayed when I attempt to modify a table name in an ApsaraDB RDS for PostgreSQL instance?

Problem description

Running the following SQL statement to rename a table to an uppercase name returns an error:

ALTER TABLE testtable RENAME TO TESTTABLE

Error returned:

ERROR: relation "testtable" already exists

Cause

PostgreSQL folds all unquoted identifiers to lowercase before processing them. When you run ALTER TABLE testtable RENAME TO TESTTABLE, PostgreSQL treats the target name TESTTABLE as testtable — identical to the source table — and rejects the rename as a conflict with an existing relation.

For example:

• TESTTABLE, testtable, and Testtable are all treated as testtable when unquoted.

• Only quoted identifiers, such as "TESTTABLE", preserve their exact case.

Solution

Wrap the new name in double quotation marks to preserve the uppercase letters:

ALTER TABLE testtable RENAME TO "TESTTABLE";

After renaming, always reference this table using the quoted form "TESTTABLE" in all subsequent SQL statements — including SELECT, INSERT, UPDATE, and DROP. Unquoted references such as testtable or TESTTABLE resolve to lowercase and will not match the quoted identifier.

Best practice

Use lowercase table names consistently to avoid case-sensitivity issues. If mixed-case or uppercase names are required, quote them in every SQL statement that references them.

Avoid alternating between quoted and unquoted references to the same table, as this leads to "relation does not exist" errors that are difficult to debug.

Applicable scope

ApsaraDB RDS for PostgreSQL