https://github.com/seancorfield/honeysql/issues/348 -- TL;DR: it's hard to support generically because it's almost entirely database-specific.

You should be able to add a general index (without primary or unique) -- see https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.891/doc/getting-started/sql-clause-reference#add-index-drop-index @U6CN6JQ22

I tried to execute the SQL generated directly on postgres, but it seems that syntax is not supported:

type "look" does not exist

What exact SQL are you trying to run there?

ALTER TABLE fruit ADD INDEX look(appearance)

Using postgresql 14

So it's expecting a type of index where you currently look. Let me look at the PG docs...

Try adding {:quoted true} to the format call. All the examples I can find of alter table .. add index .. for PG seem to use quoted names.

DDL is a giant mess and varies dramatically from database to database which is why HoneySQL hadn't used to support any DDL. I'm gradually adding the more common pieces but some of them are just really hard to add in any way that works across multiple databases.

Adding :quoted true didn’t work, it still complains. Even executing the SQL with quotes doesn’t work

ALTER TABLE fruit ADD INDEX 'look'('appearance')

It should be double quotes, not single.

ALTER TABLE "fruit" ADD INDEX "look" ("appearance")

That syntax is used in several tutorials I found for PostgreSQL so I assumed it works -- I don't use PG myself.

I also don't currently have DDL tests for this so I should add those.

still complains about type “look” not existing

OK. Thanks for testing that. The tutorials must be wrong (no surprise, I guess).

I also found a tutorial that made use of add index in postgres, wondering if they used to support it? But when looking through the docs of alter table on postgres 14 I couldn’t find generic add index supported, only for primary keys, constraints, uniques etc