Add Boolean Column PostgreSQL

Steps:

Example SQL statement:

ALTER TABLE your_table_name
ADD COLUMN is_active boolean DEFAULT TRUE;

In this example:

• DEFAULT TRUE sets the default value of the column to TRUE.
• is_active is the name of the new boolean column.
• your_table_name is replaced with the actual name of your table.

Explanation:

• The DEFAULT TRUE clause specifies that the default value for the is_active column will be TRUE for any new rows inserted into the table.
• The ADD COLUMN clause adds a new boolean column named is_active to the table.
• The ALTER TABLE command modifies the existing your_table_name table.

Additional considerations:

• For more complex scenarios, you might consider using triggers or constraints to enforce additional rules or calculations related to the new column.
• You can also use the NOT NULL constraint to ensure that the column cannot be null.
• If you want to set the default value to FALSE, replace TRUE with FALSE in the DEFAULT clause.

Method 1: Using a single ALTER TABLE statement:

ALTER TABLE your_table_name
ADD COLUMN is_active boolean DEFAULT TRUE;

This concise method directly adds the new column and sets its default value in one step. It's suitable for most scenarios where you have no existing data or want the default value to apply to all new rows.

Method 2: Adding the column first and then setting the default value:

ALTER TABLE your_table_name
ADD COLUMN is_active boolean;

UPDATE your_table_name
SET is_active = TRUE;

ALTER TABLE your_table_name
ALTER COLUMN is_active SET NOT NULL;

This approach is more flexible, allowing you to:

• Enforce non-null constraints: The final ALTER TABLE statement ensures that the column cannot be null after the initial update.
• Control the default value for existing rows: If you want to set a different default value for existing rows, you can modify the UPDATE statement accordingly.

Example using a different default value for existing rows:

ALTER TABLE your_table_name
ADD COLUMN is_active boolean;

UPDATE your_table_name
SET is_active = CASE WHEN some_condition THEN TRUE ELSE FALSE END;

ALTER TABLE your_table_name
ALTER COLUMN is_active SET NOT NULL;

Choosing the right method:

• If you need more control over the default value for existing rows or want to enforce non-null constraints, use Method 2.
• If you have no existing data or want the default value to apply to all new rows, use Method 1.

• You can set the default value to FALSE by replacing TRUE in the DEFAULT clause or UPDATE statement.
• Remember to replace your_table_name with the actual name of your table.

ALTER TABLE your_table_name
ADD COLUMN is_active boolean DEFAULT TRUE;

ALTER TABLE your_table_name
ADD COLUMN is_active boolean;

UPDATE your_table_name
SET is_active = TRUE;

ALTER TABLE your_table_name
ALTER COLUMN is_active SET NOT NULL;

ALTER TABLE your_table_name
ADD COLUMN is_active boolean;

UPDATE your_table_name
SET is_active = CASE WHEN some_condition THEN TRUE ELSE FALSE END;

ALTER TABLE your_table_name
ALTER COLUMN is_active SET NOT NULL;