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?.
-- check version
SELECT version();
| version |
|---|
| PostgreSQL 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
-- create test table
CREATE TABLE test_json (
id varchar(36) NOT NULL,
profile json NULL,
-- age: derived column, value derived from profile json
age smallint GENERATED ALWAYS AS ((profile ->> 'age')::smallint) STORED,
CONSTRAINT pk_giri_test PRIMARY KEY (id)
);
CREATE TABLE
SELECT * FROM test_json;
| id | profile | age |
|---|
SELECT 0
-- insert a record
INSERT INTO test_json
(id, profile)
VALUES('2eab2d99-167d-41b7-8227-d89ae45d3801', '{"fname":"Giri", "lname":"Pottepalem", "age":30}');
INSERT 0 1
-- check data
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
SELECT 1
-- insert another record with no profile
INSERT INTO test_json
(id)
VALUES('2eab2d99-167d-41b7-8227-d89ae45d3802');
INSERT 0 1
-- check data
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | null | null |
SELECT 2
-- try to update profile using json_set
UPDATE
test_json
SET
profile = jsonb_set(profile::jsonb, '{fname}', '"boo"'::jsonb)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that previous update did not work though it returned 1 rows affected
select * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | null | null |
SELECT 2
-- lets update using regular
UPDATE
test_json
SET
profile = '{"fname":"boo", "lname":"pottepalem"}'::jsonb
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that previous update actually updated profile
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"fname": "boo", "lname": "pottepalem"} | null |
SELECT 2
-- Update one property using jsonb_set
UPDATE
test_json
SET
profile = jsonb_set(profile::jsonb, '{age}', '15'::jsonb)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that jsonb_set now updated profile
select * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"age": 15, "fname": "boo", "lname": "pottepalem"} | 15 |
SELECT 2
-- update multiple properties using jsonb_set
UPDATE
test_json
SET
profile =
jsonb_set(
jsonb_set(
jsonb_set(
profile::jsonb,
'{age}',
'18'::jsonb
)::jsonb,
'{fname}',
'"Bhuvan"'::jsonb
)::jsonb,
'{lname}',
'"Pottepalem"'::jsonb
)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that jsonb_set now updated profile with multiple properties
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"age": 18, "fname": "Bhuvan", "lname": "Pottepalem"} | 18 |
SELECT 2
-- update json by concatening with ||, properties that exists get updated, properties that don't exist get added
UPDATE
test_json
SET
profile = profile::jsonb || '{"fname" : "boo", "lname" : "potte", "address" : "dummy address"}'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that existing properties got updated and new properties got added
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"age": 18, "fname": "boo", "lname": "potte", "address": "dummy address"} | 18 |
SELECT 2
-- remove address property from json
UPDATE
test_json
SET
profile = profile::jsonb - 'address'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that address property is removed from the json
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"age": 18, "fname": "boo", "lname": "potte"} | 18 |
SELECT 2
-- update json by concatening with ||, properties that exists get updated, properties that do not exist get added, integer age
UPDATE
test_json
SET
profile = profile::jsonb || '{"fname" : "Bhuvan"}' || '{"lname" : "Pottepalem"}' || '{"age" : 19}' || '{"address" : "dummy address"}'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
UPDATE 1
-- check data, notice that existing properties got updated and new properties got added, including generated column age
SELECT * FROM test_json;
| id | profile | age |
|---|---|---|
| 2eab2d99-167d-41b7-8227-d89ae45d3801 | {"fname":"Giri", "lname":"Pottepalem", "age":30} | 30 |
| 2eab2d99-167d-41b7-8227-d89ae45d3802 | {"age": 19, "fname": "Bhuvan", "lname": "Pottepalem", "address": "dummy address"} | 19 |
SELECT 2
-- append another column data to a JSON column
SELECT profile :: jsonb || jsonb_build_object('id', id) as profile_json_with_id_added FROM test_json;
| profile_json_with_id_added |
|---|
| {"id": "2eab2d99-167d-41b7-8227-d89ae45d3801", "age": 30, "fname": "Giri", "lname": "Pottepalem"} |
| {"id": "2eab2d99-167d-41b7-8227-d89ae45d3802", "age": 19, "fname": "Bhuvan", "lname": "Pottepalem", "address": "dummy address"} |
SELECT 2