Postgres 12

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
id	profile	age
2eab2d99-167d-41b7-8227-d89ae45d3801	{"fname":"Giri", "lname":"Pottepalem", "age":30}	30
2eab2d99-167d-41b7-8227-d89ae45d3802	null	null