PostgreSQL - ALTER DATABASE

In PostgreSQL, the ALTER DATABASE command is used to change the attributes or properties of an existing database. Here are some common uses of the ALTER DATABASE command:

1. Rename a Database

To rename an existing database:

ALTER DATABASE old_db_name RENAME TO new_db_name;

2. Change Owner of a Database

To change the owner of a database:

ALTER DATABASE db_name OWNER TO new_owner;

3. Change the Tablespace of a Database

To change the default tablespace for a database:

ALTER DATABASE db_name SET TABLESPACE new_tablespace_name;

4. Change Configuration Settings for a Database

PostgreSQL allows you to change the configuration parameters for a specific database. For instance, if you want to set the search_path for a particular database:

ALTER DATABASE db_name SET search_path TO myschema,public;

5. Allow or Disallow Connections

To disallow new connections to a specific database:

ALTER DATABASE db_name WITH ALLOW_CONNECTIONS false;

And to allow them again:

ALTER DATABASE db_name WITH ALLOW_CONNECTIONS true;

6. Set Connection Limit

To set a connection limit on a database:

ALTER DATABASE db_name WITH CONNECTION LIMIT 100;

This will allow only up to 100 concurrent connections to the db_name database.

Points to Remember:

You can't execute ALTER DATABASE within a transaction block.
The name of the database can't start with a number or special character unless it's quoted.
Only the database owner or a superuser can change the configuration settings of a database.
Before making any changes, especially in a production environment, it's recommended to take a backup and ensure that you understand the implications of the changes you're making.

Renaming a database in PostgreSQL:
- Description: Changing the name of an existing PostgreSQL database.
- Code Example:
```
ALTER DATABASE old_name RENAME TO new_name;
```
Changing the owner of a database in PostgreSQL:
- Description: Modifying the owner of a PostgreSQL database.
- Code Example:
```
ALTER DATABASE your_database OWNER TO new_owner;
```

Adding or removing options with ALTER DATABASE in PostgreSQL:

Description: Adjusting configuration options for a PostgreSQL database.

Code Examples:

-- Adding an option
ALTER DATABASE your_database SET option_name = option_value;

-- Removing an option
ALTER DATABASE your_database RESET option_name;

Setting default privileges for a database in PostgreSQL:
- Description: Establishing default privileges for new objects created in a database.
- Code Example:
```
ALTER DATABASE your_database DEFAULT PRIVILEGES GRANT ALL ON TABLES TO new_role;
```
Altering the character set and collation of a database in PostgreSQL:
- Description: Modifying the character set and collation for a PostgreSQL database.
- Code Example:
```
ALTER DATABASE your_database
SET character_set = 'new_character_set',
    collate = 'new_collation';
```
Modifying the connection limit for a PostgreSQL database:
- Description: Adjusting the maximum number of concurrent connections allowed for a database.
- Code Example:
```
ALTER DATABASE your_database
CONNECTION LIMIT new_limit;
```

PostgreSQL ALTER DATABASE vs ALTER SCHEMA:

Description: Differentiating between altering databases and schemas in PostgreSQL.

Code Example:

-- ALTER DATABASE
ALTER DATABASE your_database SET option_name = option_value;

-- ALTER SCHEMA
ALTER SCHEMA your_schema SET option_name = option_value;

Renaming tables and functions within a database with ALTER DATABASE:
- Description: Renaming tables and functions within a specific PostgreSQL database.
- Code Example:
```
ALTER DATABASE your_database RENAME TABLE old_table TO new_table;
ALTER DATABASE your_database RENAME FUNCTION old_function() TO new_function();
```