Pgsql alter emits invalid ADD INDEX SQL and replay fails on existing ADD COLUMN

[cjzamora]

What version of Inquire are you using?

stackpress/inquire-pg@0.10.5, stackpress@0.10.5

What OS are you experiencing this issue?

macOS 26.5, arm64

Describe the Bug

Pgsql alter emits invalid ADD INDEX SQL and replay fails on existing ADD COLUMN

Title

Pgsql alter emits invalid ADD INDEX SQL and replay fails on existing
ADD COLUMN

Inquire Version

• @stackpress/inquire-pg: 0.10.5
• stackpress: 0.10.5

OS

• macOS 26.5
• arm64

Expected Behaviour

When the Pgsql dialect alters an existing table to add a new indexed relation
column, it should emit Postgres-compatible SQL.

Adding an index should be emitted as a standalone CREATE INDEX statement:

CREATE INDEX "profile_company_id_index" ON "profile" ("company_id");

If migration replay safety is intended, the generated SQL should also tolerate
statements that were already applied during a previous failed run:

ALTER TABLE "profile" ADD COLUMN IF NOT EXISTS "company_id" VARCHAR(255) DEFAULT NULL;
CREATE INDEX IF NOT EXISTS "profile_company_id_index" ON "profile" ("company_id");
DROP INDEX IF EXISTS "profile_company_id_index";

Actual Behaviour

The Pgsql alter path emits MySQL-style index syntax:

ALTER TABLE "profile" ADD COLUMN "company_id" VARCHAR(255) DEFAULT NULL
ALTER TABLE "profile" ADD INDEX "profile_company_id_index" ("company_id")

The index statement fails on Postgres/CockroachDB:

error: at or near "(": syntax error
source SQL:
ALTER TABLE "profile" ADD INDEX "profile_company_id_index" ("company_id")
                                                           ^

After that failed run, replaying the migration can fail earlier because the
column already exists:

ALTER TABLE "profile" ADD COLUMN "company_id" VARCHAR(255) DEFAULT NULL
error: column "company_id" of relation "profile" already exists

To Reproduce

1. Use @stackpress/inquire-pg@0.10.5 with a Postgres-compatible database.

2. Start with an existing table, for example:

   CREATE TABLE "profile" (
     "id" VARCHAR(255) NOT NULL,
     PRIMARY KEY ("id")
   );

3. Generate or run an alter/diff that adds a nullable relation column and index
   to the existing table, equivalent to:

   ALTER TABLE "profile" ADD COLUMN "company_id" VARCHAR(255) DEFAULT NULL
   ALTER TABLE "profile" ADD INDEX "profile_company_id_index" ("company_id")

4. Run the generated SQL against Postgres or CockroachDB.

5. Observe the syntax error on ALTER TABLE ... ADD INDEX.

6. If the column statement was committed before the index failure, rerun the
   migration.

7. Observe the replay failure on column "company_id" of relation "profile" already exists.

Notes

This surfaced while adding a direct Stackpress relation from Company to
Profile, where Profile.companyId is the local relation field and
Company.profiles is the one-to-many relation.

The create table path appears to already emit standalone CREATE INDEX, so
this looks specific to alter/diff migrations.

Local Workaround

We patched our app's pg connection boundary to rewrite generated alter
statements:

• ALTER TABLE "t" ADD COLUMN ... -> ALTER TABLE "t" ADD COLUMN IF NOT EXISTS ...
• ALTER TABLE "t" ADD INDEX "idx" (...) -> CREATE INDEX IF NOT EXISTS "idx" ON "t" (...)
• ALTER TABLE "t" DROP INDEX "idx" -> DROP INDEX IF EXISTS "idx"

That allowed the schema relation migration to continue without replacing the
direct relation with JSON/reference metadata.

Expected Behavior

Expected Behaviour

When the Pgsql dialect alters an existing table to add a new indexed relation
column, it should emit Postgres-compatible SQL.

Adding an index should be emitted as a standalone CREATE INDEX statement:

CREATE INDEX "profile_company_id_index" ON "profile" ("company_id");

If migration replay safety is intended, the generated SQL should also tolerate
statements that were already applied during a previous failed run:

ALTER TABLE "profile" ADD COLUMN IF NOT EXISTS "company_id" VARCHAR(255) DEFAULT NULL;
CREATE INDEX IF NOT EXISTS "profile_company_id_index" ON "profile" ("company_id");
DROP INDEX IF EXISTS "profile_company_id_index";

To Reproduce

To Reproduce

1. Use @stackpress/inquire-pg@0.10.5 with a Postgres-compatible database.

2. Start with an existing table, for example:

   CREATE TABLE "profile" (
     "id" VARCHAR(255) NOT NULL,
     PRIMARY KEY ("id")
   );

3. Generate or run an alter/diff that adds a nullable relation column and index
   to the existing table, equivalent to:

   ALTER TABLE "profile" ADD COLUMN "company_id" VARCHAR(255) DEFAULT NULL
   ALTER TABLE "profile" ADD INDEX "profile_company_id_index" ("company_id")

4. Run the generated SQL against Postgres or CockroachDB.

5. Observe the syntax error on ALTER TABLE ... ADD INDEX.

6. If the column statement was committed before the index failure, rerun the
   migration.

7. Observe the replay failure on column "company_id" of relation "profile" already exists.

Include git repo/fork so we can easily reproduce the issue

No response