PostgreSQL - 如何根据PSQL VIEW中的公共唯一标识符将NULL值替换为来自另一列的值

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?

我的PSQL视图中有三个外部标识符。我怎么能用基于公共first_id的third_id值替换NULL second_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.

应填充second_id列中的NULL值,即不应有空白单元格。

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.

如果second_id列与third_id列共享一个值,则此值应填充second_id列中的空白单元格。

They should both be based on their common first_id.

它们都应该基于它们共同的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.

second_id实际上是修改third_id时的情况。在视图中进行此修改。

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).

以下是视图正在使用的表定义的链接(my_table)。

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

Thanks again for your help.

再次感谢你的帮助。

2 个解决方案

#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:

您无法更新基础表my_table,因为它没有second_id列,因此您应该使视图以您希望的方式显示数据。 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.

这假设你的my_table.localization_key ='rq.bkd'确实有1个third_id值;如果不是,您应该添加适当的限定符,例如ORDER BY first_id ASC NULLS LAST LIMIT 1或其他一些合适的过滤器。另请注意,CTE是JOINed,而不是LEFT JOINed,假设始终存在没有NULL的有效对(first_id,third_id)。

#2

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

#1