By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
create table table_name (
data jsonb null,
column_name text null
);
insert into table_name (data, column_name)
values ('{"object_key_name": "hello_world"}', null)
,('{"object_key_name": "goodby_world"}', 10);
select *
from table_name;
CREATE TABLE
INSERT 0 2
| data | column_name |
|---|---|
| {"object_key_name": "hello_world"} | null |
| {"object_key_name": "goodby_world"} | 10 |
SELECT 2
select *
from table_name
where column_name = null;
| data | column_name |
|---|
SELECT 0
select *
from table_name
where column_name <> null;
| data | column_name |
|---|
SELECT 0
select *
from table_name
where column_name is null
| data | column_name |
|---|---|
| {"object_key_name": "hello_world"} | null |
SELECT 1
select *
from table_name
where column_name is not null;
| data | column_name |
|---|---|
| {"object_key_name": "goodby_world"} | 10 |
SELECT 1
-- your test
update table_name
set column_name = data ->> 'object_key_name'
where column_name is null;
select *
from table_name;
UPDATE 1
| data | column_name |
|---|---|
| {"object_key_name": "goodby_world"} | 10 |
| {"object_key_name": "hello_world"} | hello_world |
SELECT 2
insert into table_name (data, column_name)
values ('{"object_key_name": "nothing here"}', null);
update table_name
set column_name = data ->> 'object_key_name'
where coalesce(column_name, 'it''null folks') = 'it''null folks';
select *
from table_name;
INSERT 0 1
UPDATE 1
| data | column_name |
|---|---|
| {"object_key_name": "goodby_world"} | 10 |
| {"object_key_name": "hello_world"} | hello_world |
| {"object_key_name": "nothing here"} | nothing here |
SELECT 3