CODEWITHSUNDEEP
postgresql
rshar
rshar

Reputation: 1475

Set value of a column based on another column

I have the following table in Postgres 11.

col1    col2  source    col3
a       abc   curation  rejected
a       abc   DB 
b       etg   DB        accepted
c       jfh   curation

How can I assign value in col3 based on the values in col1

The expected output is:

col1    col2  source    col3
a       abc   curation  rejected
a       abc   DB        rejected
b       etg   DB        accepted
c       jfh   curation  null

Is there a way to check if values in col1 and col2 in subsequent rows are identical, then assign same col3 to all the rows (if the col3 value in other row is null).

Any help is highly appreciated.

Upvotes: 0

Views: 3736

Answers (2)

jian
jian

Reputation: 4877

update a2 set
                col3 =
                  case when col1 = 'a' then 'rejected'
                  when col1 = 'b' then 'accepted'
                  when col1 = 'c' then 'null' end
where col3 is  null
returning *;

You can also set triggers. But generated columns only available from 12. So you need upgrade to use generated columns.
db fiddle

Upvotes: 1

Gurmokh
Gurmokh

Reputation: 2081

You're not entirely clear on what the criteria is, but at a basic level it could depend on how you want to query this data, there are multiple ways you could do this.

Generated Columns

drop table if exists atable ; 

CREATE TABLE atable (
 cola text , 
 colb text GENERATED ALWAYS AS (case when cola='a' then 'rejected' else 
null end) STORED
);

insert into atable(cola) values ('a')

A View.

create or replace view aview as 
select cola, case when cola='a' then 'rejected' else null end as colb 
from atable;

Both would yield the same results.

cola|colb    |
----+--------+
a   |rejected|

Other options could be a materialized view, simple query logic.

You have options.

Upvotes: 1

Related Questions