I've written a thing to generate CREATE INDEX statements using honeysql. The final SQL list destined for jdbc looks sane to me...

["CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> ?)) WHERE _type = ?"
 "email"
 "account"]

org.postgresql.util.PSQLException: ERROR: there is no parameter $2
                                     Position: 113
    java.sql.BatchUpdateException: Batch entry 0 CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> 'email')) WHERE _type = 'account' was aborted: ERROR: there is no parameter $2
                                     Position: 113  Call getNextException to see other errors in the batch.

stumped. (and coincidentally, I could have sworn this worked two weeks ago.)

@xlevus How are you actually executing that generated SQL?

(and what does that weird-looking syntax ((_data -> 'email')) actually mean??)

@seancorfield it's a postgres operator for getting an item out of a json object/array https://www.postgresql.org/docs/current/static/functions-json.html

@tanzoniteblack use the plain function instead of syntatic sugar https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb

@myguidingstar 🙂 I'm not the one with an issue. And I don't think he's trying to use the ? operator. He's using the -> operator: (_data -> ?)

sorry I misread your question

and the ? is supposed to be a parameter insertion

I suspect the problem here is in how @xlevus actually executes the SQL. It's using BatchUpdateException which suggests he's using execute! but I don't think all drivers allow DDL to be executed that way. I think db-do-commands is needed here.

Ah, but db-do-commands doesn't accept SQL parameters, just SQL statements (for DDL).

I'm using the same syntax and functions to do queries too.

source: https://github.com/xlevus/docufant-clj/blob/master/src/docufant/db.clj#L84

and the index object in question would look like {:type :account :path [:email] :unique true}

@xlevus Can you share enough of the stacktrace so I can tell exactly what in java.jdbc is throwing that exception? Also, which version of java.jdbc are you using?

just pumping it into a repl now

(jdbc/with-db-transaction [conn (config/db)]
                         (jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
                         (jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> 'field')) WHERE (data_type = 'dog');"]))

but with the param:

the code that generated that exception:

(jdbc/with-db-transaction [conn (config/db)]
                         (jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
                         (jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> 'field')) WHERE (data_type = ?);" "dog"]))

the second message came from a slightly different query, hence the complaint about $2

(jdbc/with-db-transaction [conn (config/db)]
                         (jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
                         (jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> ?)) WHERE (data_type = ?);" "dog" "cat"]))

I wonder if there's something about the Hikari proxy causing trouble?

hm, no, I get the same thing using just a db-spec

OK, good to eliminate that as the source of a problem.

i wonder if it's postgres

And which pg driver are you using?

https://github.com/xlevus/docufant-clj/blob/master/project.clj

[org.clojure/clojure "1.9.0"]
                 [org.clojure/java.jdbc "0.7.3"]
[org.postgresql/postgresql "42.1.4"]

removing another source of confusion, it's not related to the json stuff:

(jdbc/with-db-transaction [conn (config/db)]
                         (jdbc/execute! conn ["CREATE TABLE test_table (json_column text, data_type text);"])
                         (jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table(json_column) where data_type = ?;" "data_type"]))

and I'm testing this with [org.postgresql/postgresql "42.2.0"]

maybe it's postgres.

docufant_test> PREPARE dfidx(a, b) AS
                 CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> $1)) WHERE _type = $2;

syntax error at or near "CREATE"
LINE 2:   CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__ac...
          ^

Time: 0.005s

My gut instinct says you can't run DDL with parameters through a PreparedStatement but I don't know if that's true.

@seancorfield that's the same thing I'm thinking. Can't find anything either way on the topic so far

I think that's the origin of why db-do-commands doesn't use a prepared statement -- it just creates a basic statement based on the SQL (only, no parameters).

looks like that might be the problem.

guess I'm going to have to manually escape ... stuff.

Cheers for the help guys

oh lord D: manual escaping.