0

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 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?

1 Answer 1

0

You need to use names in CREATE INDEX statements, for example:

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 entities_title_idx ON entities (title);
CREATE INDEX IF NOT EXISTS entities_description_idx ON entities (description);
1
  • Yeah but there is no way to get the name of the index knowing its table and the target column, as far as I know. You either just have to know CREATE INDEX ON <table_name> (<column_name>); under the hood creates an index name in the form {table_name}_{column_name}_idx{sequence} or you have to get into the running instance and check created indexes one-by-one, which can be quite non-trivial/impossible to do. Commented Aug 11, 2023 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.