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:
So what is the right way to conditionally recreate "default" indexes like this?
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
Last activity: Jun 24, 2025, 06:08 AM