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

Question

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.

Is the data that you show indeed from a view, or is it from a table? And if it is from a view, is that view then "updatable" (data coming from a single table and no row modifying qualifiers such as GROUP BY, DISTINCT, UNION, etc)? — Patrick
– Patrick, Commented May 3, 2015 at 1:16
@Patrick - unfortunately the view isn't updatable. I didn't realize this until now. The row_number is partitioned, and I assume CASE WHEN statements qualify as row modifiers too - I guess I need to work through this a bit more because it would be ideal to get all this into a view rather than a new table. — Dan Kreiger
– Dan Kreiger, Commented May 3, 2015 at 20:10
Typically a view is made updatable by using an INSTEAD OF trigger. However, in this case you seem to have a one-off situation (update second_id once and then you are done) so a simple UPDATE query is more logical. Can you post the definition of the view and that of the table where second_id comes from? — Patrick
– Patrick, Commented May 4, 2015 at 0:11
Thanks a lot @Patrick. I added the table and view definitions. — Dan Kreiger
– Dan Kreiger, Commented May 4, 2015 at 15:52

Houari · Accepted Answer · 2015-05-02 22:26:55Z

3

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

edited May 2, 2015 at 22:26

answered May 2, 2015 at 22:18

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Kreiger Over a year ago

Thanks so much. This works perfectly for a table, so maybe I can create a new table instead of a view. Ideally I'd like to be able to apply this to a view if that's possible. Right now I get this error message:

ERROR:  cannot update view "my_view"  DETAIL:  Views that return window functions are not automatically updatable.  HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

I'm trying to work through it a bit because my view isn't immediately updatable - it looks like @Patrick warned me about this

Patrick · Accepted Answer · 2015-05-05 03:51:33Z

1

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.

answered May 5, 2015 at 3:51

Patrick

33k7 gold badges73 silver badges102 bronze badges

1 Comment

Patrick Over a year ago

Thanks Dan. In true SO fashion, you should accept this answer as correct (tick the grey check mark below the voting buttons on the left). You may even up-vote this (up-triangle) as this is how good answers (and questions) float to the top of the lists.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related