Sample Header Ad - 728x90

How to create a table with only indexes and no actual data?

0 votes
1 answer
143 views
**Question:** Is is possible to create a table in PostgreSQL for the sole purpose of creating specialized indexes (used for advanced search use-cases), without storing duplicate data in such tables? **Context:** I have a table with a JSONB column that can contain dynamic (and nested keys). I've managed to create a function, called jsonb_flatten which can convert {a: {b: "val"}} to {"a.b": "val"}. However, I need trigram indexes on all possible values, which is not support by regular JSONB indexes, i.e. GIST/GIN. One solution could be to duplicate this data into a table with two fields key | value | original_row_id and create a trigram index on the value column, but that would end-up unnecessarily duplicating the data. Thus, my question.
Asked by Saurabh Nanda (333 rep)
Jul 13, 2024, 04:29 PM
Last activity: Aug 10, 2025, 03:06 AM