Menu
I've wrote a simple script to reindex our PG databases once a week. It has a simple logic:
And being proper lazy developer I dislike this concept that I have to constantly update my hashmap. (On the other hand, I have found two inefficient indices while doing that.)
pg_index seems quite informative, is there a way to reconstruct a create index clause from it?
It's easy to get columns list, but we use different index types, different opclasses for fields, partial indices... And who knows what. It's important to be sure that we will get exact the same clause that was used to create index in the first place.
692
ALTER command to add and drop INDEXALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
32) Why we need to create an index if the primary key is already present in a table? Primary key can store null value, whereas a unique key cannot store null value.
Syntax. The syntax to create an index in SQL is: CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ... column_n);
Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX .
Yep, there's a built-in function pg_get_indexdef for the purpose.
e.g.:
regress=> SELECT pg_get_indexdef('demo_pkey'::regclass);
pg_get_indexdef
--------------------------------------------------------
CREATE UNIQUE INDEX demo_pkey ON demo USING btree (id)
(1 row)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
