CODEWITHSUNDEEP
postgresql
comte
comte

Reputation: 3344

How to rename a table inside a schema?

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'

Upvotes: 107

Views: 81185

Answers (3)

samzna
samzna

Reputation: 435

SET search_path TO domain;
ALTER TABLE IF EXISTS old_table_name RENAME TO new_table_name;

Upvotes: 0

Salad.Guyo
Salad.Guyo

Reputation: 3395

  1. Switch to your database
machine$\c my_database
  1. Tename the db
my_databse=# alter table old_name rename to new_name;

Upvotes: -2

Bohuslav Burghardt
Bohuslav Burghardt

Reputation: 34776

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.

Upvotes: 181

Related Questions