PostgreSQL ALTER DATABASE Statement

Summary: In this tutorial, you’ll learn how to use the PostgreSQL ALTER DATABASE statement to modify the properties of an existing database.

PostgreSQL ALTER DATABASE Statement Overview #

To change the properties of an existing PostgreSQL database, you use the ALTER DATABASE statement:

ALTER DATABASE database_name
[WITH] option;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following table displays the standard options:

The ALTER DATABASE statement can be helpful in changing database settings, ownership, connection limits, and tablespace assignments.

Note that only a superuser or the database owner has the privilege to execute an ALTER DATABASE statement.

We’ll create a new database to demonstrate the ALTER DATABASE statement:

First, connect to your PostgreSQL server using psql:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new database called db:

CREATE DATABASE db;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Renaming a Database #

The following statement renames the database db to mydb:

ALTER DATABASE db
RENAME TO mydb;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that you cannot rename a database while connected to it. Additionally, the database you want to rename must have no active connections.

Changing the Owner #

First, create a new role john with the LOGIN privilege:

CREATE ROLE john
WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, change the ownership of the mydb to john:

ALTER DATABASE mydb
OWNER TO john;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Only the owner of the mydb database or a superuser can execute this command. After this, john is the new owner of the mydb. It means that john has all privileges on the mydb database.

Setting a Configuration Parameter #

The following statement changes the work_mem setting for mydb:

ALTER DATABASE mydb
SET work_mem TO '64MB';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This new work_mem applies to all new connections to the mydb database.

Resetting a Configuration Parameter #

The following statement resets work_mem to its default value:

ALTER DATABASE mydb
RESET work_mem;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Limiting the Number of Connections #

The following statement sets the number of concurrent connections to 50:

ALTER DATABASE mydb
WITH CONNECTION LIMIT 50;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Moving a Database to a New Tablespace #

The following statement creates a new tablespace and moves the mydb to the new tablespace:

First, create a new tablespace:

CREATE TABLESPACE mydb_ts
LOCATION 'C:\pgdata\primary';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace the path to the tablespace with your path.

Second, change the tablespace of the mydb database:

ALTER DATABASE mydb
SET TABLESPACE mydb_ts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the ALTER DATABASE statement to modify the properties of an existing database.