PostgreSQL – Alter Database with Examples

In this tutorial, we will see the ALTER DATABASE command in PostgreSQL with some examples. Note that, the ALTER DATABASE and the ALTER TABLE are two different commands in PostgreSQL, do not get confused. So, without wasting the time, let’s get started!

Also read: PostgreSQL – How to Create a Database

Introduction to ALTER DATABASE In PostgreSQL

In PostgreSQL, the ALTER DATABASE command is used to modify the characteristics of a database. This command allows you to change the name of a database, set or change its default tablespace, and change the owner of the database.

To use this command, you must be the owner of the database or a superuser. Note that, superusers have all the privileges automatically.

Now let’s see the different forms of the ALTER DATABASE command with syntax and examples.

Syntax of ALTER DATABASE in PostgreSQL

There are multiple operations you can perform using the ALTER DATABASE command. Let’s see all of them one by one.

Change Attributes of the Database

You can change the attributes of the database such as connections, isTemplate, etc using this command. To perform the below actions, you must be a database owner or a superuser.

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplateCode language: CSS (css)

Here,

• allowconn – Boolean value. If set to false, no one can connect to the database.
• connlimit – Number of concurrent connections. -1 for no limit.
• istemplate – Boolean value. If true, any user with CREATEDB capabilities can clone this database; if false, only superusers or the database owner can do so.

Rename the Database

You can rename the database using the ALTER DATABASE command.

ALTER DATABASE name RENAME TO new_name

Here,

• name – current name of the database that you want to rename
• new_name – new name of the database

Change Owner of the Database

You can also change the owner of a database using the OWNER TO clause. For example, to change the owner of a database named “mydb” to a role named “new_owner”, you would use the following command:

ALTER DATABASE mydb OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

You need to be the owner of the database, a direct or indirect member of the new owning role, and possess the CREATEDB privilege in order to change the owner.

Here,

• mydb – the database name
• new_owner/current_rolw/current_user/session_user – new owner of the database

Other Operations

The ALTER DATABASE command is also used to perform the following operations.

Change the Tablespace

To set or change the default tablespace for a database, you can use the SET TABLESPACE clause. For example, to set the default tablespace for a database named “mydb” to “new_tablespace”, you would use the following command:

ALTER DATABASE mydb SET TABLESPACE new_tablespace;

Update Database Collation Version

You can update the collation version of the database, you can use the following command:

ALTER DATABASE name REFRESH COLLATION VERSION

PostgreSQL ALTER DATABASE Examples

Now let’s see some real-time examples of the ALTER DATABASE based on the above syntaxes.

First login to the psql shell from the terminal.

Rename the Database

Let’s change the database name.

ALTER DATABASE jd RENAME TO journaldev;

Change Owner of the Database

Let’s change the owner of the database from ‘postgres’ to ‘tejas’.

ALTER DATABASE journaldev OWNER TO tejas;

Change Database Settings

We can change the database settings such as the number of concurrent connections, connection permission etc.

ALTER DATABASE journaldev
WITH ALLOW_CONNECTIONS true CONNECTION LIMIT 1;Code language: JavaScript (javascript)

Here, we have allowed the connection to the database and changed the connection limit to 1.

Conclusion

In this tutorial, we have seen that the ALTER DATABASE command in PostgreSQL is a powerful tool that allows users to make various modifications to an existing database, such as renaming it, changing its owner, or modifying its configuration settings. It’s important to keep in mind that this command should be used with caution as some modifications may affect the overall functionality and performance of the database

Reference

PostgreSQL Official documentation.