Alban Hertroys <haramrae@gmail.com> writes:
> On 25 September 2015 at 13:08, Ramesh T <rameshparnanditech@gmail.com> 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