-- -- persistence.sql -- Table storage, flatten/unflatten roundtrips, SELECT queries. -- Covers both collection (text-keyed) and icollection (int-keyed). -- -- ============================================================ -- PART 1: collection table storage -- ============================================================ CREATE TABLE select_collection(col_collection collection); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"USA":"Washington", "UK":"London"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"India":"New Delhi"}}'); INSERT INTO select_collection VALUES('{"entries":{"China":"Beijing"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"NULL":"NULL"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"Canada":"Ottawa"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"India":"New Delhi"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{"Australia":"Canberra"}}'); -- should throw error INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.text","entries":{NULL:NULL}}'); ERROR: Invalid format LINE 1: INSERT INTO select_collection VALUES('{"value_type":"pg_cata... ^ SELECT * FROM select_collection; col_collection ------------------------------------------------------------------------------------- {"value_type": "pg_catalog.text", "entries": {"USA": "Washington", "UK": "London"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"China": "Beijing"}} {"value_type": "pg_catalog.text", "entries": {"NULL": "NULL"}} {"value_type": "pg_catalog.text", "entries": {"Canada": "Ottawa"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"Australia": "Canberra"}} (7 rows) SELECT sort(col_collection) FROM select_collection; sort ------------------------------------------------------------------------------------- {"value_type": "pg_catalog.text", "entries": {"UK": "London", "USA": "Washington"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"China": "Beijing"}} {"value_type": "pg_catalog.text", "entries": {"NULL": "NULL"}} {"value_type": "pg_catalog.text", "entries": {"Canada": "Ottawa"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"Australia": "Canberra"}} (7 rows) SELECT key(col_collection) FROM select_collection; key ----------- USA India China NULL Canada India Australia (7 rows) SELECT value(col_collection) FROM select_collection; value ------------ Washington New Delhi Beijing NULL Ottawa New Delhi Canberra (7 rows) SELECT value_type(col_collection) FROM select_collection; value_type ------------ text text text text text text text (7 rows) SELECT to_table(col_collection) FROM select_collection; to_table ---------------------- (USA,Washington) (UK,London) (India,"New Delhi") (China,Beijing) (NULL,NULL) (Canada,Ottawa) (India,"New Delhi") (Australia,Canberra) (8 rows) SELECT keys_to_table(col_collection) FROM select_collection; keys_to_table --------------- USA UK India China NULL Canada India Australia (8 rows) SELECT values_to_table(col_collection) FROM select_collection; values_to_table ----------------- Washington London New Delhi Beijing NULL Ottawa New Delhi Canberra (8 rows) SELECT COUNT(col_collection) FROM select_collection; count ------- 2 1 1 1 1 1 1 (7 rows) INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.varchar","entries":{"Japan":"Tokyo"}}'); INSERT INTO select_collection VALUES('{"value_type":"pg_catalog.char","entries":{"Canada":"Ottawa"}}'); SELECT * FROM select_collection; col_collection ------------------------------------------------------------------------------------- {"value_type": "pg_catalog.text", "entries": {"USA": "Washington", "UK": "London"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"China": "Beijing"}} {"value_type": "pg_catalog.text", "entries": {"NULL": "NULL"}} {"value_type": "pg_catalog.text", "entries": {"Canada": "Ottawa"}} {"value_type": "pg_catalog.text", "entries": {"India": "New Delhi"}} {"value_type": "pg_catalog.text", "entries": {"Australia": "Canberra"}} {"value_type": "character varying", "entries": {"Japan": "Tokyo"}} {"value_type": "pg_catalog.\"char\"", "entries": {"Canada": "O"}} (9 rows) DROP TABLE select_collection; -- Typed collection persistence CREATE TABLE select_typed(id serial, c collection); INSERT INTO select_typed(c) SELECT add(add(null::collection, 'a', 42::bigint), 'b', 99::bigint); INSERT INTO select_typed(c) SELECT add(add(null::collection, 'x', '2026-01-01'::date), 'y', '2026-06-15'::date); INSERT INTO select_typed(c) SELECT add(add(null::collection, 'p', 'hello'), 'q', null::text); SELECT id, c FROM select_typed ORDER BY id; id | c ----+-------------------------------------------------------------------------------------- 1 | {"value_type": "bigint", "entries": {"a": "42", "b": "99"}} 2 | {"value_type": "pg_catalog.date", "entries": {"x": "01-01-2026", "y": "06-15-2026"}} 3 | {"value_type": "pg_catalog.text", "entries": {"p": "hello", "q": null}} (3 rows) DROP TABLE select_typed; -- ============================================================ -- PART 2: icollection table storage -- ============================================================ -- Table storage tests for icollection (mirrors collection select.sql) CREATE TABLE select_icollection(col_icollection icollection); INSERT INTO select_icollection VALUES('{"value_type":"pg_catalog.text","entries":{"1":"Washington", "2":"London"}}'); INSERT INTO select_icollection VALUES('{"value_type":"pg_catalog.text","entries":{"3":"New Delhi"}}'); INSERT INTO select_icollection VALUES('{"value_type":"pg_catalog.text","entries":{"4":"Beijing"}}'); INSERT INTO select_icollection VALUES('{"value_type":"pg_catalog.text","entries":{"5":"Ottawa"}}'); SELECT * FROM select_icollection; col_icollection ---------------------------- {1: Washington, 2: London} {3: New Delhi} {4: Beijing} {5: Ottawa} (4 rows) SELECT sort(col_icollection) FROM select_icollection; sort ---------------------------- {1: Washington, 2: London} {3: New Delhi} {4: Beijing} {5: Ottawa} (4 rows) SELECT key(col_icollection) FROM select_icollection; key ----- 1 3 4 5 (4 rows) SELECT value(col_icollection) FROM select_icollection; value ------------ Washington New Delhi Beijing Ottawa (4 rows) SELECT value_type(col_icollection) FROM select_icollection; value_type ------------ text text text text (4 rows) SELECT to_table(col_icollection) FROM select_icollection; to_table ----------------- (1,Washington) (2,London) (3,"New Delhi") (4,Beijing) (5,Ottawa) (5 rows) SELECT keys_to_table(col_icollection) FROM select_icollection; keys_to_table --------------- 1 2 3 4 5 (5 rows) SELECT values_to_table(col_icollection) FROM select_icollection; values_to_table ----------------- Washington London New Delhi Beijing Ottawa (5 rows) SELECT COUNT(col_icollection) FROM select_icollection; count ------- 2 1 1 1 (4 rows) DROP TABLE select_icollection; -- Typed icollection persistence CREATE TABLE select_ic_typed(id serial, ic icollection); INSERT INTO select_ic_typed(ic) SELECT add(add(null::icollection, 1, 42::bigint), 2, 99::bigint); INSERT INTO select_ic_typed(ic) SELECT add(add(null::icollection, 10, '2026-01-01'::date), 20, '2026-06-15'::date); INSERT INTO select_ic_typed(ic) SELECT add(add(null::icollection, 1, 'hello'), 2, null::text); SELECT id, ic FROM select_ic_typed ORDER BY id; id | ic ----+---------------------------------- 1 | {1: 42, 2: 99} 2 | {10: 01-01-2026, 20: 06-15-2026} 3 | {1: hello, 2: null} (3 rows) DROP TABLE select_ic_typed;