[ADMIN] Unique text index on a non-text column

[Craig James]

I'd like to enforce text uniqueness on a non-text column (it's of type "molecule" from a third-party plugin). The third-party plugin doesn't support unique indexes. My guess was something like this, but it doesn't work:

create table molecules(id integer primary key, moltext molecule('my-type'));

create unique index i_unique_molecule on molecules(moltext::text);

The "molecule" column does contain an ordinary string, and when selected returns ordinary text data.

Is there a way to do this? (And if so, what did I miss in the documentation?)

Thanks,

Craig

[David G. Johnston]

​It helps to show what "doesn't work" actually is.

https://www.postgresql.org/docs/9.6/static/sql-createindex.html

Note the extra set of paretheses surrounding "expression".

​create unique index i_unique_molecule on molecules ((cast(moltext as text)))

((moltext::text)) might work...

David J.

[Craig James]

On Fri, Jul 15, 2016 at 10:31 AM, David G. Johnston <david.g....@gmail.com> wrote:

On Fri, Jul 15, 2016 at 1:26 PM, Craig James <cja...@emolecules.com> wrote:

I'd like to enforce text uniqueness on a non-text column (it's of type "molecule" from a third-party plugin). The third-party plugin doesn't support unique indexes. My guess was something like this, but it doesn't work:

create table molecules(id integer primary key, moltext molecule('my-type'));

create unique index i_unique_molecule on molecules(moltext::text);

The "molecule" column does contain an ordinary string, and when selected returns ordinary text data.

Is there a way to do this? (And if so, what did I miss in the documentation?)

​It helps to show what "doesn't work" actually is.

https://www.postgresql.org/docs/9.6/static/sql-createindex.html

Note the extra set of paretheses surrounding "expression".

Ah, I'd overlooked that.

 

​create unique index i_unique_molecule on molecules ((cast(moltext as text)))

((moltext::text)) might work...

Thanks, that did the trick.

Craig

David J.

--

---------------------------------
Craig A. James

Chief Technology Officer

eMolecules, Inc.

---------------------------------