PostgreSQL - ADD COLUMN

In PostgreSQL, the ALTER TABLE command is used to add a new column to an existing table. Here's the general syntax for adding a new column:

ALTER TABLE table_name ADD COLUMN column_name data_type [DEFAULT default_value] [constraints];

table_name: Name of the table to which you want to add the new column.
column_name: Name of the new column you're adding.
data_type: Data type of the new column.
DEFAULT default_value: An optional default value for the new column.
constraints: Any additional constraints you want to apply to the new column (e.g., NOT NULL).

Examples:

Adding a simple column:
To add a new column named email of type VARCHAR(255) to the users table:
```
ALTER TABLE users ADD COLUMN email VARCHAR(255);
```
Adding a column with a default value:
To add a new column named is_active of type BOOLEAN with a default value of TRUE to the users table:
```
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
```
Adding a column with a constraint:
To add a new column named age of type INTEGER that should always have a value (i.e., it's not nullable) to the users table:
```
ALTER TABLE users ADD COLUMN age INTEGER NOT NULL;
```
Adding a column with a default value and constraint:
To add a new column named registration_date of type DATE with a default value of the current date and ensuring it's not nullable to the users table:
```
ALTER TABLE users ADD COLUMN registration_date DATE DEFAULT CURRENT_DATE NOT NULL;
```

Points to Note:

You cannot add a column with a NOT NULL constraint if the table has existing rows unless you provide a default value. This is because the new column would have null values for all existing rows, which would violate the NOT NULL constraint.
Remember to thoroughly test any schema changes in a development environment before applying them to a production database to ensure that they work as expected and don't introduce unintended issues.

Adding a new column to a PostgreSQL table:
- Description: Expanding an existing table by introducing a new column.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN new_column_name data_type;
```

Examples of ALTER TABLE ADD COLUMN in PostgreSQL:

Description: Providing additional examples of adding columns with different data types.

Code Examples:

-- Adding an integer column
ALTER TABLE your_table
ADD COLUMN new_column_integer INT;

-- Adding a text column with a default value
ALTER TABLE your_table
ADD COLUMN new_column_text TEXT DEFAULT 'default_value';

Default values for a new column in PostgreSQL:
- Description: Setting default values for a newly added column.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN new_column_with_default INT DEFAULT 42;
```
Adding multiple columns in a single ALTER TABLE statement in PostgreSQL:
- Description: Introducing multiple columns in a single ALTER TABLE command.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN column1 INT,
ADD COLUMN column2 TEXT,
ADD COLUMN column3 DATE;
```
Changing column data type with ADD COLUMN in PostgreSQL:
- Description: Modifying the data type of a column during its addition.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN new_column_name VARCHAR(255);
```
Constraints when using ADD COLUMN in PostgreSQL:
- Description: Considering constraints, such as NOT NULL or unique constraints.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN new_column_with_constraint INT NOT NULL UNIQUE;
```

PostgreSQL ADD COLUMN vs ALTER COLUMN:

Description: Understanding the difference between adding a new column and altering an existing column.

Code Example:

-- Adding a new column
ALTER TABLE your_table
ADD COLUMN new_column_name INT;

-- Altering an existing column
ALTER TABLE your_table
ALTER COLUMN existing_column_name SET NOT NULL;

Handling NULL values during ADD COLUMN in PostgreSQL:
- Description: Managing NULL values during the addition of a new column.
- Code Example:
```
ALTER TABLE your_table
ADD COLUMN new_column_name INT DEFAULT 0;
```