Re: Constraint?

On Wed, 11 Dec 2002, David Fetter wrote:

> Kind people,
>
> I'm trying to create a table as follows:
>
> CREATE TABLE media (
> media_id SERIAL NOT NULL PRIMARY KEY
> , media_type_id INTEGER NOT NULL REFERENCES media_type(media_type_id)
> , media_title VARCHAR(255) NOT NULL
> , UNIQUE(LOWER(media_title), media_type_id)
> );
>
> This barfs with a parse error.
>

> If I leave out the LOWER() part, it works, but I want the thing to be
> unique up to case. What's to do?

I think the unique() syntax wants columns only. You could get a
functionally similar constraint by using a unique functional index, except
that there the problem is that functional indexes only work as
function(list of cols).

You can make a function of your own that takes both columns and does the
lower and then combines them in some fashion and unique on that function.

That doesn't help you for searches on just the lower() part, however.