Ramesh T wrote:
> CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y' then load_id else null end );
>
> how can i convert case expressed to postgres..above it is oracle.
CREATE TABLE pick (picked char(1), load_id integer);
CREATE FUNCTION picked_loadid(character, integer) RETURNS integer
IMMUTABLE STRICT LANGUAGE sql AS
$$SELECT CASE WHEN $1 = 'y' THEN $2 ELSE NULL END$$;
CREATE INDEX idx_load_pick ON pick (picked_loadid(picked, load_id));
*but*
It will only work with queries like:
SELECT * FROM pick WHERE picked_loadid(picked, load_id) IS NOT NULL;
Yours,
Laurenz Albe
