Re: Unique text index on a non-text column

On Fri, Jul 15, 2016 at 1:26 PM, Craig James <cjames(at)emolecules(dot)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".

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

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

David J.