Postgres 9.4

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

CREATE TABLE elbat
            (nmuloc1 integer,
             nmuloc2 integer,
             nmuloc3 integer);

CREATE TABLE


INSERT INTO elbat
            VALUES (1,
                    2,
                    3),
                   (NULL,
                    3,
                    4),
                   (NULL,
                    6,
                    NULL),
                   (NULL,
                    NULL,
                    NULL);

INSERT 0 4


SELECT json_object_agg(jet.k, jet.v),
       json_agg(jet.k),
       array_agg(jet.k),
       string_agg(jet.k, ',')
       FROM elbat t
            CROSS JOIN LATERAL row_to_json(t) rtj(j)
            CROSS JOIN LATERAL json_each_text(rtj.j) jet(k, v)
       WHERE jet.v IS NULL
       GROUP BY rtj.j::text;


json_object_agg
json_agg
array_agg
string_agg


{ "nmuloc1" : null, "nmuloc3" : null }
["nmuloc1", "nmuloc3"]
{nmuloc1,nmuloc3}
nmuloc1,nmuloc3

{ "nmuloc1" : null, "nmuloc2" : null, "nmuloc3" : null }
["nmuloc1", "nmuloc2", "nmuloc3"]
{nmuloc1,nmuloc2,nmuloc3}
nmuloc1,nmuloc2,nmuloc3

{ "nmuloc1" : null }
["nmuloc1"]
{nmuloc1}
nmuloc1



SELECT 3

json_object_agg	json_agg	array_agg	string_agg
{ "nmuloc1" : null, "nmuloc3" : null }	["nmuloc1", "nmuloc3"]	{nmuloc1,nmuloc3}	nmuloc1,nmuloc3
{ "nmuloc1" : null, "nmuloc2" : null, "nmuloc3" : null }	["nmuloc1", "nmuloc2", "nmuloc3"]	{nmuloc1,nmuloc2,nmuloc3}	nmuloc1,nmuloc2,nmuloc3
{ "nmuloc1" : null }	["nmuloc1"]	{nmuloc1}	nmuloc1