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?
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.
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
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 JOIN
ed, not LEFT JOIN
ed, assuming there is always a valid pair (first_id, third_id)
without NULL
s.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With