Sample Header Ad - 728x90

How to conditionally recreate indexes created by `CREATE INDEX ON <table_name> (<column_name>)`?

0 votes
1 answer
1140 views
I am squashing migrations and rewriting all constructs like:
CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  description text
);

CREATE INDEX ON entities (title);
CREATE INDEX ON entities (description);
Into:
CREATE TABLE IF NOT EXISTS entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  description text
);

CREATE INDEX IF NOT EXISTS ON entities (title);
CREATE INDEX IF NOT EXISTS ON entities (description);
When I run the squash I get a syntax error on the CREATE INDEX IF NOT EXISTS ON entities (title); line. After squinting harder at the [docs reference](https://www.postgresql.org/docs/13/sql-createindex.html) I noticed IF NOT EXISTS does in fact require name after it.
So what is the right way to conditionally recreate "default" indexes like this?
Asked by Biller Builder (288 rep)
Aug 10, 2023, 02:23 PM
Last activity: Jun 24, 2025, 06:08 AM