Extract Data Definitions for Indexes from Postgresql Using `Pg_Index` Table
I'm Trying to Extract Data Definitions from a Postgresql Database for Indexes into the Application. the Structure in My Application, Currently, Looks Something...
I'm trying to extract data definitions from a PostgreSQL database for indexes into the application.
The structure in my application, currently, looks something like this:
type IndexColExpr = {
col_or_expr: string;
};
type IncludeColumn = {
name: string;
};
type Index = {
id: number;
table_id: number;
table_name: string;
name: string;
unique: boolean;
method: string;
columns: IndexColExpr[];
include_columns: IncludeColumn[];
where: string;
};
I'm using the pg_class and pg_index to get the information that I need.
So far everything is going well until I get to the part where the index is on expressions (not a simple column name). Those expressions are stored in pg_index.indexprs which is of the type pg_node_tree.
The problem is when there are multiple expressions in the index; they will all be encoded inside the indexprs column as a single pg_node_tree value and I need them separated. That means I have to parse pg_node_tree to get individual expressions. Or I can convert pg_node_tree to SQL string using pg_get_expr(), but I still have to parse the SQL DDL script. Since they are in a list format (expr1, expr2) I can't simply cast to text[]. Both pg_node_tree and the DDL script can contain arbitrary expressions, so I can't use a simple split-by-comma to get them.
Is there something in PostgreSQL that will allow me to extract individual expressions without parsing pg_node_tree or the DDL script?
If there isn't, then maybe I shouldn't bother with pg_index and just parse the CREATE INDEX query provided by pg_get_indexdef()?
1 Answer
There are two overloaded variants of pg_get_indexdef():
If you only pass the OID of the index, you get the complete reconstructed
CREATE INDEXDDL script.But if you also pass the
smallintnumber of the index expression ( =pg_attribute.attnum) and aboolean"pretty" flag, you get the definition of that index column. As the manual puts it:If column is supplied and is not zero, only the definition of that column is reconstructed.
To get all index columns of a given index separately, and in order (plain column references and expressions alike):
SELECT pg_get_indexdef(a.attrelid, a.attnum, true)
FROM pg_attribute a
WHERE a.attrelid = 'my_index'::regclass -- your index name here
ORDER BY a.attnum;
I am going to pg_attribute directly, we don't need to involve pg_index for this. If you need the connection:
pg_index.indexrelid = pg_attribute.attrelid
If there can be ambiguity, schema-qualify the index name:
'my_schema.my_index'::regclass
This does not include any decorators per index column like DESC NULLS LAST, operator class, collation, ... Those are encoded separately in indoption, indclass, indcollation of the table pg_index.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
