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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
