DDL Commands For Database

CREATE DATABASE

CREATE DATABASE command to create a new database. It allows specifying optional configurations like ownership, encoding, collation, and templates.

Syntax

CREATE DATABASE database_name
WITH OWNER = owner_name // optional
ENCODING = 'UTF8' // optional
LC_COLLATE = 'en_US.UTF-8' // optional
LC_CTYPE = 'en_US.UTF-8' // optional
TEMPLATE = template0; // optional

DROP DATABASE

The SQL DROP DATABASE statement is used to delete an existing database along with all the data such as tables, views, indexes, stored procedures, and constraints.

Syntax:

DROP DATABASE [IF EXISTS] database_name [WITH (FORCE)];

Parameters

• IF EXISTS → Prevents an error if the database does not exist.
• WITH (FORCE) → Terminates active connections before dropping (useful when other users are connected).

Basic Usage

DROP DATABASE my_database;

🔹 This removes my_database but fails if it doesn’t exist.

IF EXISTS

Avoid Error if Database Doesn’t Exist

DROP DATABASE IF EXISTS my_database;

🔹 Ensures PostgreSQL doesn’t throw an error if the database is already deleted.

WITH (FORCE)

Terminate Active Connections

DROP DATABASE my_database WITH (FORCE);

🔹 Useful when the database has active sessions that prevent deletion.

ALTER DATABASE

The ALTER DATABASE command is used to modify an existing database’s properties.

• Change the attributes of the database
• Rename the database
• Change the owner of the database
• Change the default tablespace of a database
• Change the session default for a run-time configuration variable for a database

Syntax:

ALTER DATABASE database_name action;

Common Actions

Rename Database

ALTER DATABASE old_name RENAME TO new_name;

🔹 Changes the database name (cannot be connected to it while renaming).

Change Owner

ALTER DATABASE database_name OWNER TO new_owner;

🔹 Transfers ownership to another user.

Set Configuration

ALTER DATABASE database_name SET parameter = value;

🔹 Configures settings like timezone or encoding.

RESET Configuration

ALTER DATABASE database_name RESET parameter;

🔹 Resets a parameter to its default value.

Change Connection Limits

ALTER DATABASE database_name WITH CONNECTION LIMIT 50;

🔹 Restricts the maximum number of concurrent connections.

// TODO: Write From Here