Question 232785 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – LEFT JOIN query which updates values in the left table

Whenever we join two tables with common names, those common names are duplicated. But what if you want to do a left join and replace values (especially NULL values)? For updating a table you can use the MERGE operator but what about in a query?

The following tables are clear documentation of the desired functionality. How can we achieve this in a query? e.g.

  SELECT * 
    FROM table1 LEFT JOIN table2 USING(date) 
                LEFT_MERGING(col2)
    -- LEFT_MERGING prefers the left-hand col2 value if there is one.

Table1:

date        | col1 | col2  
------------+------+------
'2023-08-01'|    1 |  20
'2023-08-02'|    2 | NULL
'2023-08-03'|    3 | NULL
'2023-08-04'|    4 | NULL
'2023-08-05'|    5 | NULL
'2023-08-06'|    6 | NULL

Table 2:

 date        | col2
 ------------+------
 '2023-08-03'|  22
 '2023-08-04'|  23

AIM:

date        | col1 | col2  
------------+------+------
'2023-08-01'|    1 |  20
'2023-08-02'|    2 | NULL
'2023-08-03'|    3 |  22
'2023-08-04'|    4 |  23
'2023-08-05'|    5 | NULL
'2023-08-06'|    6 | NULL
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This can be achieved using the COALESCE function and a bit of basic machinery:

    SELECT   date 
       , col1
       , COALESCE(table1.col2, table2.col2) AS col2
  FROM table1 
    LEFT JOIN table2 USING(date)

    COALESCE( x_1, x_2, ... , x_n) returns the first argument which is not NULL or returns NULL if all its arguments are NULL.


  2. 0

    With the proper logic, you can achieve the desired functionality using the COALESCE function and a LEFT JOIN. The COALESCE function returns the first non-NULL value in a list of expressions.

    Here’s how you can achieve the AIM result using a combination of LEFT JOIN and COALESCE:

    SELECT
    t1.date,
    t1.col1,
    COALESCE(t2.col2, t1.col2) AS col2
    FROM
    table1 t1
    LEFT JOIN
    table2 t2 ON t1.date = t2.date USING(date);

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search