CODEWITHSUNDEEP
postgresql
StellaMaris
StellaMaris

Reputation: 867

Set value in select statement

I have two views A and B created by other views (cant use a rule ON UPDATE DO INSTEAD). A and B have also the same three columns text_1, text_2, count. Till now my SQL looks like:

select A.text_1, A.text_2, COALESCE(b.count,a.count) as count 
from A 
   left join B on A.text_1 like B.text_1 and A.text_2 like B.text_2;

But I need another rule to set the value of count to 0 of all records where text_1 like text_2 in my result of the select statement above.

I'm using views because I like to use the "CREATE OR REPLACE..." statement.

Upvotes: 0

Views: 3525

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125494

using make it simpler and the cast from boolean to integer avoids a verbose case

select
    text_1, text_2,
    coalesce(b.count, a.count) * (not text_1 = text_2)::int as count 
from
    a 
    left join
    b using (text_1, text_2)

Upvotes: 2

Related Questions