Syntax to Use the ALTER DATABASE Command
The syntax to use the ALTER DATABASE command is as follows.
[ RENAME TO new_name
| OWNER TO new_owner
| CONNECTION LIMIT { limit | UNLIMITED }
| COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ]
[ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
The database_name parameter is the name of the database you want to modify the attributes of.
The RENAME TO parameter is used to rename or modify the name of the database. There are some limitations while using the RENAME TO parameter, like ALTER DATABASE command does not allow modifying the name of the current database. Also, you can not rename the template0, template1, dev or padb_harvest database in Redshift, and only the superuser has the privilege to rename the database.
The OWNER TO parameter is used to change the current existing owner of the database. Unlike the RENAME TO parameter, the OWNER TO parameter can be used to change the owner of the current database or any other database. But the important thing is that only the superuser has the privilege to change the owner of the database.
The CONNECTION LIMIT (limit | unlimited) parameter can be used to modify the limit of the maximum number of database connections users are allowed to make with the database at the same time. If you want to allow the unlimited connection, then use the unlimited argument. By default, the connection limit in redshift is unlimited.
The COLLATE (case_sensitive | case_insensitive) parameter is used to specify if the string search is case sensitive or not. In order to change this attribute, you just need to have the privilege of the current database. Superusers have the privilege to change the state of case sensitivity. Also, the user that has the privilege to create a database can also change the state of case sensitivity.
The ISOLATION LEVEL (serializable | snapshot) parameter is used to set the isolation level that you want to use while running the queries in your database. The isolation level in redshift provides two different options to isolate for the users. The first option is serializable isolation which provides the full privilege of serializability for side-by-side or concurrent transactions. The other option is snapshot isolation which is used when you want to protect your data against repetitive updates and delete disputes.
There are a few things that you need to know before using the isolation option with the ALTER DATABASE command. Only the superuser or the user that has permission to create database commands has the privilege to use the isolation level in the database. No user has the privilege to use the isolation level command on the dev database. In case any other user is connected to the database, the isolation level command will fail.
Examples of Using the ALTER DATABASE Command
Now in this section, we will discuss some examples of the ALTER DATABASE command with different parameters to alter the different attributes of the Redshift database. There are the following examples of the ALTER DATABASE command.
- Rename the database
- Modifying the Owner
- Updating connection limit
- Update the database string search case sensitivity
- Isolation level using a snapshot
Rename the Database
In this example, we will discuss how to modify the name of the database in Redshift using the ALTER DATABASE command. Redshift does not allow you to modify the current database, so you can rename the other existing databases. Let’s suppose you have a database having the name user_db, and you want to rename it to employee_db. Use the following query.
The above query, when run in Redshift, will change the name of the user_db database to employee_db.
Modifying the Owner
In this example, we will see how to change the owner of the database in Redshift using the ALTER DATABASE command. Redshift allows you to update the owner of the current or any other database. Let the existing owner of a database named user_db is user_1, and you want to change the owner of the database to user_2. Use the following query.
The above query, when executed in the Redshift, will change the owner of the user_db from user_1 to user_2.
Updating Connection Limit
By default, the Redshift allows users to create unlimited connections with the database, but a connection limit can be applied to limit the concurrent connections with the Redshift. In this section, we will see how we can update the connection limit on Redshift using the ALTER DATABASE command.
Let us have a users database, and by default, there is no connection limit on it. Now we want to apply a connection limit so that only 100 concurrent connections are allowed on the users db. The following query can be used to modify the connection limit on the Redshift database.
After you execute the query, only 100 database connections will be allowed concurrently, and other connections will be denied.
Update the Database String Search Case Sensitivity
In this example, we will see how to change the string search case sensitivity of a database using the ALTER DATABASE command in the Redshift. Redshift allows you to modify the case sensitivity of the current database only. Let’s suppose we have a database named user_db, and we want to change the case sensitivity of this database. The following ALTER DATABASE query can be used to change the case sensitivity of the database search.
The above query, when executed in the Redshift, will change the string search case sensitivity of the user_db.
Isolation Level Using a Snapshot
In this example, we will see how to change the isolation level using the snapshot option in Redshift using the ALTER DATABASE command. Let’s suppose we have a database named user_db, and we want to change the isolation level of the database with a snapshot. The following ALTER DATABASE query with the ISOLATION LEVEL parameter can be used to change the isolation level of the database.
The above query, when executed in the Redshift, will change the user_db with the SNAPSHOT isolation level.
Conclusion
In this blog, we have discussed how we can use the ALTER DATABASE command to modify the different attributes of the database. There are multiple attributes that can be changed using the Redshift ALTER DATABASE command, and in this article, we have mentioned some examples like updating the name of the database, changing the owner of the database, changing the isolation level and updating the string search case sensitivity of the database using the ALTER DATABASE command.
