CODEWITHSUNDEEP
sqlpostgresql
fencingfraction
fencingfraction

Reputation: 33

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

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?

Upvotes: 0

Views: 1978

Answers (2)

user8193706
user8193706

Reputation: 2425

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;

Upvotes: 2

LukStorms
LukStorms

Reputation: 29677

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

Upvotes: 0

Related Questions