CODEWITHSUNDEEP
sqlperlpostgresqldbix-class
Ha Sh
Ha Sh

Reputation: 764

How to create a unique index that is case-insensitive using DBIx::Class

I am using Postgres and I was hoping to create a case insensitive unique constraint on my database using DBIx::Class.

I understand that an example like:

__PACKAGE__->add_unique_constraint("table_unique_constraint", ["col_name"]);

would create a unique constraint on a given table.

I am looking for a way to create a case insensitive unique constraint using DBIx::Class to better but it I would say I want to create a unique index that. The SQL query that I am trying to generate is something like:

CREATE UNIQUE INDEX table_unique_constraint ON "table_name"(lower("column_name"));

This example makes sure that I check for entries to be inserted against values in the table without taking into account the case.

Is there any way to add this to the Result? Esp, the catch being the lower().

And of course the DBIx::Class Manual directed me to create constraint instead of a hook for unique index.

Upvotes: 1

Views: 307

Answers (1)

Alexander Hartmaier
Alexander Hartmaier

Reputation: 2204

DBIx::Class itself doesn't create DDL queries, so your question doesn't apply. If you use $schema->deploy, that uses SQL::Translator under the hood. I don't think DBIC can hold such special metadata to control the unique key creation but look at the SQL::Translator docs to be really sure. I suggest to create the DDL statements with deployment_statements and modifying the generated ones or use an after deploy method modifier that executes alter SQL statements to make the generated constraints case insensitive.

Upvotes: 2

Related Questions