Alban Hertroys <haramrae@xxxxxxxxx> writes: > On 25 September 2015 at 13:08, Ramesh T <rameshparnanditech@xxxxxxxxx> 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. > BTW, your CASE statement isn't exactly valid, even in Oracle. Your > comparison is in fact this: picked = picked='y'. Yeah. Aside from that confusion, the other reason this command doesn't work as-is is you need more parentheses. An expression in an index has to either look like a function call or be parenthesized. So: regression=# create table pick (picked text, load_id int); CREATE TABLE regression=# CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y' then load_id else null end ); ERROR: syntax error at or near "case" regression=# CREATE UNIQUE INDEX idx_load_pick ON pick ((case picked when picked='y' then load_id else null end )); ERROR: operator does not exist: text = boolean regression=# CREATE UNIQUE INDEX idx_load_pick ON pick ((case when picked='y' then load_id else null end )); CREATE INDEX regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
