From: | Craig James <cjames(at)emolecules(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Unique text index on a non-text column |
Date: | 2016-07-15 17:36:26 |
Message-ID: | CAFwQ8rfw0mfWkLg6hiY9hma-gmJXk7ziFQazdZ-DZS03Lh0xnQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Fri, Jul 15, 2016 at 10:31 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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".
>
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.
---------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | John Scalia | 2016-07-18 12:44:35 | if anyone is running slony, I have a trivial question |
Previous Message | David G. Johnston | 2016-07-15 17:31:54 | Re: Unique text index on a non-text column |