honeysql 2021-08-31 | Slack Archive

another question, do we have create index command https://www.postgresql.org/docs/current/sql-createindex.html, it seem different with add-index above?

CREATE INDEX ON foo (title);

quan04:08:43

yeah, I just want to quick check if honeysql already support that.

seancorfield05:08:39

@quan Is there a reason to use CREATE INDEX ON <table> ... rather than ALTER TABLE <table> ADD <index> ... ?

seancorfield05:08:08

SQL/DDL is insanely variable so it's really kind of hard to support everything... 🙂

seancorfield05:08:01

Ugh! Because PG doesn't let you alter table and add an index concurrently...

👍 1

seancorfield05:08:29

Fine (gods, I hate PostgreSQL!) go create an issue on GH and I'll look at adding it...

👀 1

seancorfield05:08:52

PG is far and away the most work of any database I ever have to support, both for HoneySQL and for next.jdbc 😞

🙂 1

quan05:08:01

thanks! I'm fine just using jdbc query for now.

quan05:08:34

for delete-from helper, how can I add alias for delete table. I try with this but it's not expected

(-> (delete-from [:foo :a])
    (using [:bar :b])
    (where [:= :a.id :b.id])
    sql/format)
=> ["DELETE FROM FOO(a) USING bar b WHERE a.id = b.id"]

seancorfield05:08:39

It's late. I'm in bed. I'll look at that tomorrow

🙏 1

seancorfield16:08:58

@quan Is this what you're looking for in that delete-from?

dev=> (-> (h/delete-from :foo :a)
 #_=>     (h/using [:bar :b])
 #_=>     (h/where [:= :a.id :b.id])
 #_=>     (sql/format))
["DELETE FROM foo a USING bar b WHERE a.id = b.id"]

👍 2

quan10:09:41

ah yeah, thank @seancorfield. For me look like behavior of delete-from is inconsistent with another function, e.g using bellow. It seem not clear to have (h/delete-from :foo :a) based on the doc 🙂

seancorfield16:09:20

It wasn't a syntax I expected. I'll need to look at the docs for the various databases, and also in more detail at that specific formatter.

seancorfield17:09:41

@quan I double-checked and delete-from is formatted like a select except that it expects a single form, however it doesn't add AS which probably makes more sense so I'll update the as filter so delete-from is formatted that way. I still need to double-check a few databases' docs to verify that is safe.

seancorfield17:09:16

Looks like that might be PG only, but I think alias support is harmless -- delete from foo a ... will work on PG and won't work on other DBs, so it would be clearer to support delete from foo as a ... I think.

👍 2

seancorfield17:09:25

(that'll be in the next release -- but you have a working solution now)

seancorfield16:08:47

Here's the issue for create index https://github.com/seancorfield/honeysql/issues/348 -- it's hard because that's completely vendor-specific syntax (and not compatible between PG and MS SQL -- I haven't even looked at Oracle or MySQL there. DDL is all over the map across different DBs.

quan10:09:33

yeah, I'm not work much with MySQL recently, so not aware these different..

seancorfield16:08:33

For comparison, here's MySQL which is different again https://dev.mysql.com/doc/refman/5.7/en/create-index.html

2021-08-31

Channels