Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL - How can I replace NULL values with values from another column based on a common unique identifier in a PSQL VIEW

I have three foreign identifiers in my PSQL view. How could I replace the NULL second_id values with the third_id values based on their common first_id?

Currently:


 first_id | second_id | third_id 
----------+-----------+----------
        1 |           |      11
        1 |           |      11
        1 |           |      11
        1 |        22 |      22
        2 |        33 |      33
        3 |        44 |      44
        4 |        55 |      55
        5 |        66 |      66
        6 |           |      77
        6 |           |      77
        6 |           |      77
        6 |           |      77
        6 |        88 |      88

Should be:


 first_id | second_id | third_id 
----------+-----------+----------
        1 |        22 |      11
        1 |        22 |      11
        1 |        22 |      11
        1 |        22 |      22
        2 |        33 |      33
        3 |        44 |      44
        4 |        55 |      55
        5 |        66 |      66
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      88

How can I make this change?

  • The NULL values in the second_id column should be filled i.e. there shouldn't be blank cells.
  • If the second_id column shares a value with the third_id column, this value should fill the blank cells in the second_id column.
  • They should both be based on their common first_id.
  • Thanks so much. I really appreciate it.


    The second_id is really a CASE WHEN modification of the third_id. This modification is made in the view.

    VIEW:

        View "public.my_view"
                   Column            |            Type             | Modifiers | Storage  | Description 
        -----------------------------+-----------------------------+-----------+----------+-------------
         row_number                  | bigint                      |           | plain    | 
         first_id                    | integer                     |           | plain    | 
         second_id                   | integer                     |           | plain    | 
         third_id                    | integer                     |           | plain    | 
         first_type                  | character varying(255)      |           | extended | 
         date_1                      | timestamp without time zone |           | plain    | 
         date_2                      | timestamp without time zone |           | plain    | 
         date_3                      | timestamp without time zone |           | plain    | 
        View definition:
         SELECT row_number() OVER (PARTITION BY t.first_id) AS row_number,
            t.first_id,
                CASE
                    WHEN t.localization_key::text = 'rq.bkd'::text THEN t.third_id
                    ELSE NULL::integer
                END AS second_id,
            t.third_id,
            t.first_type,
                CASE
                    WHEN t.localization_key::text = 'rq.bkd'::text THEN t.created_at
                    ELSE NULL::timestamp without time zone
                END AS date_1,
                CASE
                    WHEN t.localization_key::text = 'st.appt'::text THEN t.created_at
                    ELSE NULL::timestamp without time zone
                END AS date_2,
                CASE
                    WHEN t.localization_key::text = 'st.eta'::text THEN t.created_at
                    ELSE NULL::timestamp without time zone
                END AS date_3
           FROM my_table t
          WHERE (t.localization_key::text = 'rq.bkd'::text OR t.localization_key::text = 'st.appt'::text OR t.localization_key::text = 'st.eta'::text) AND t.first_type::text = 'thing'::text
          ORDER BY t.created_at DESC;

    Here is a link to the table definition that the view is using (my_table).

    https://gist.github.com/dankreiger/376f6545a0acff19536d

    Thanks again for your help.

    like image 869
    Dan Kreiger Avatar asked May 02 '15 19:05

    Dan Kreiger


    2 Answers

    You can get it by:

    select a.first_id, coalesce(a.second_id,b.second_id), a.third_id 
    from my_table a 
    left outer join
        (
        select first_id, second_id from my_table
        where second_id is not null 
        ) b
        using (first_id)
    

    So the update should be:

    update my_table a set second_id = b.second_id
      from 
      (
      select first_id, second_id from my_table 
      where second_id is not null 
      ) b
    where b.first_id = a.first_id and a.second_id is null
    
    like image 99
    Houari Avatar answered Nov 15 '22 10:11

    Houari


    You can not UPDATE the underlying table my_table because it does not have the second_id column so you should make the view display the data the way you want it. That is fairly straightforward with a CTE:

    CREATE VIEW my_view AS
      WITH second (first, id) AS (
        SELECT first_id, third_id
        FROM my_table
        WHERE t.localization_key = 'rq.bkd')
      SELECT
        row_number() OVER (PARTITION BY t.first_id) AS row_number,
        t.first_id,
        s.id AS second_id,
        t.third_id,
        t.first_type,
        CASE
          WHEN t.localization_key = 'rq.bkd' THEN t.created_at
        END AS date_1,
        CASE
          WHEN t.localization_key = 'st.appt' THEN t.created_at
        END AS date_2,
        CASE
          WHEN t.localization_key = 'st.eta' THEN t.created_at
        END AS date_3
      FROM my_table t
      JOIN second s ON s.first = t.first_id
      WHERE (t.localization_key = 'rq.bkd'
         OR t.localization_key = 'st.appt'
         OR t.localization_key = 'st.eta')
        AND t.first_type = 'thing'
      ORDER BY t.created_at DESC;
    

    This assumes that where my_table.localization_key = 'rq.bkd' you do have exactly 1 third_id value; if not you should add the appropriate qualifiers such as ORDER BY first_id ASC NULLS LAST LIMIT 1 or some other suitable filter. Also note that the CTE is JOINed, not LEFT JOINed, assuming there is always a valid pair (first_id, third_id) without NULLs.

    like image 30
    Patrick Avatar answered Nov 15 '22 09:11

    Patrick