ALTER DATABASE ... RENAME TO

On this page

The RENAME TO clause is part of ALTER DATABASE, and changes the name of a database.

Synopsis

Required privileges

To rename a database, the user must be a member of the admin role or must have the CREATEDB parameter set.

Parameters

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Limitations

It is not possible to rename a database if:

• The database is referenced by a view. For more details, see View Dependencies.

• The database is explicitly specified in a reference to a sequence. In this case, you can drop the column in the table that references the sequence, or you can modify the reference so that it does not specify the database name.

  For example, suppose you create a database db, and in that database, a sequence seq:

  > CREATE DATABASE db;
    USE db;
    CREATE SEQUENCE seq;
  

  Then you reference the sequence in a table tab:

  > CREATE TABLE tab (
    id UUID DEFAULT gen_random_uuid(),
    count INT DEFAULT nextval('db.seq')
  );
  

  Attempting to rename the database will result in an error:

  > SET sql_safe_updates=false;
    ALTER DATABASE db RENAME TO mydb;
  

  ERROR: cannot rename database because relation "db.public.tab" depends on relation "db.public.seq"
  SQLSTATE: 2BP01
  HINT: you can drop the column default "count" of "db.public.seq" referencing "db.public.tab" or modify the default to not reference the database name "db"
  

  In order to rename the database db, you need to drop or change the reference in the default value for the seq column to not explicitly name the database db:

  > ALTER TABLE tab ALTER COLUMN count SET DEFAULT nextval('seq');
  

  > USE defaultdb;
    ALTER DATABASE db RENAME TO mydb;
  

Examples

Rename a database

> CREATE DATABASE db1;

> SHOW DATABASES;

  database_name
-----------------
  db1
  defaultdb
  movr
  postgres
  system
(5 rows)

> ALTER DATABASE db1 RENAME TO db2;

> SHOW DATABASES;

  database_name
-----------------
  db2
  defaultdb
  movr
  postgres
  system
(5 rows)

Rename fails (new name already in use)

> ALTER DATABASE db2 RENAME TO movr;

ERROR: the new database name "movr" already exists
SQLSTATE: 42P04

See also

• CREATE DATABASE
• SHOW DATABASES
• SET DATABASE
• DROP DATABASE
• Other SQL Statements

Was this helpful?