SELECT fields from table having some conditions

Question

I have two tables
1. Named as t1
2. Named as t2

example of the tables are :

t1 :

 id  name 
 1   one 
 2   two 
 3   three  

t2 :

 id  name 
 2   five 

I need to select all the data from the t1 table as well as from table t2 but I don't want to write data from table t1 which is updated

I need a resulting table like this:

 id  name 
 1   one 
 2   five 
 3   three  

Please help me to solve this in postgres

Answer 1

SELECT
    t1.ID,
    CASE
WHEN t1."name" != t2."name" then
    t2.name
ELSE 
    t1.name
END as fname
FROM
    t1 LEFT JOIN t2 ON t1.ID = t2.ID

from comments - duplicates, mean You have duplicates (answer with coalesce return the same), variant for avoid duplicates:

SELECT T1.id,
       MAX(COALESCE(t2.name, t1.name)) as name
FROM t1
LEFT JOIN t2
     ON t1.id = t2.id
GROUP BY t1.id

but - be careful, which name You need to take? FIRST/MIN/MAX? because id=2, in t2 can have name = four/seven/eight ... which right?

Answer 2

The cleanest has to be:

SELECT T1.id
       COALESCE(t2.name, t1.name) as name
FROM t1
LEFT JOIN t2
     ON t1.id = t2.id