Postgres - Add a column to a table with a default value, but set existing rows to a different defaul-CodePudding

I want to add a boolean column to a table and have the default value be false for newly created rows, but all existing rows should be set to true. How can I do that?

CodePudding user response：

First alter table and add column as alter table table_name add column column_name boolean default false;

Then update value of that column as

update table_name set column_name=true;

CodePudding user response：

A simplified test to demonstrate.

create table test (
 id int primary key
);

insert into test (id) values (1), (2), (3);

alter table test 
 add column truth boolean default false;

update test set truth = true;

insert into test (id) values (4), (5);

select * from test;

id | truth
-: | :----
 1 | t    
 2 | t    
 3 | t    
 4 | f    
 5 | f

db<>fiddle here