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 toTRUE
.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 theis_active
column will beTRUE
for any new rows inserted into the table. - The
ADD COLUMN
clause adds a new boolean column namedis_active
to the table. - The
ALTER TABLE
command modifies the existingyour_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
, replaceTRUE
withFALSE
in theDEFAULT
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 replacingTRUE
in theDEFAULT
clause orUPDATE
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;
sql postgresql