How to rename a table inside a schema?
PostgresqlPostgresql Problem Overview
I'm using PostgreSQL 9.x, I want to rename a table. This SQL code:
CREATE TABLE new (id int);
ALTER TABLE new RENAME TO old;
DROP TABLE old;
renames the table correctly. But this SQL code:
CREATE SCHEMA domain;
CREATE TABLE domain.old (id int);
ALTER TABLE domain.old RENAME TO domain.new;
fails, with error:
> ERROR: syntax error at or near "."
The "." underlined is the one between 'domain' and 'new'
Postgresql Solutions
Solution 1 - Postgresql
One way to do this:
ALTER TABLE domain.old RENAME TO new
Other way:
SET search_path TO domain;
ALTER TABLE old RENAME TO new;
Documentation for search_path
.
Solution 2 - Postgresql
SET search_path TO domain;
ALTER TABLE IF EXISTS old_table_name RENAME TO new_table_name;
Solution 3 - Postgresql
- Switch to your database
machine$\c my_database
- Tename the db
my_databse=# alter table old_name rename to new_name;