How to create dynamically partial index ?

[kalev...@gmail.com]

I have an unique constraint basically like this:

CREATE UNIQUE INDEX unique_value

  ON user_configuration (value)

  WHERE user_configuration.property = 4711;

But how it is possibly to do it dynamically ?

  dsl.createTableIfNotExists(USER_CONFIGURATION).

                column(USER_CONFIGURATION.ID, SQLDataType.INTEGER.identity(true)).

                .....

                column(USER_CONFIGURATION.VALUE, SQLDataType.VARCHAR).

                column(USER_CONFIGURATION.PROPERTY, SQLDataType.INTEGER).

                constraints(

                        constraint("USER_CONFIGURATION.ID").primaryKey(USER_CONFIGURATION.ID),

                        constraint("USER_CONFIGURATION. VALUE").unique( USER_CONFIGURATION. VALUE.  XXXX )

                ).execute();

I would expect smthng like that

XXXX = USER_CONFIGURATION.PROPERTY.eq(4711)

but does not work

Postgresql 9.5 & JOOQ 3.9.1

[Lukas Eder]

Hi Kalev,

That's an interesting question. PostgreSQL doesn't allow any such syntax on the CREATE TABLE statement:

https://www.postgresql.org/docs/current/static/sql-createtable.html

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ ...
  UNIQUE index_parameters |
  ... }
...

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ ...
  UNIQUE ( column_name [, ... ] ) index_parameters |
  ... }
...

And then:

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

However, you're not citing the CREATE TABLE statement in your example SQL statement, but the CREATE INDEX statement. Why not just use that with jOOQ?

dsl.createUniqueIndex("unique_value")

   .on(USER_CONFIGURATION, USER_CONFIGURATION.VALUE)

   .where(USER_CONFIGURATION.PROPERTY.eq(4711))

   .execute();

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.