Thread: to pg

On 25.9.2015 13:08, 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.
>
> any help appreciated...

Hello.

And what about a partial unique index as documented here:

http://www.postgresql.org/docs/9.4/static/indexes-partial.html

I.e.:

CREATE UNIQUE INDEX ON  pick (load_id) WHERE picked = 'y';

HTH,

Ladislav Lenart

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

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.

Assuming that your queries are written in such a way that Oracle is
indeed using that index and you want your queries to use the index as
well in PG:

CREATE UNIQUE INDEX idx_load_pick ON (load_id) WHERE CASE picked WHEN
'y' THEN load_id ELSE NULL END IS NOT NULL;

That's definitely written a bit redundantly, that's Oracle's fault.

If your queries aren't like that, it's as Ladislav wrote. Much simpler in PG!


To make Oracle use your original index, your queries are probably of a
form containing snippets like:

SELECT *
FROM foo
WHERE CASE picked WHEN 'y' THEN load_id ELSE NULL END IS NOT NULL

BTW, your CASE statement isn't exactly valid, even in Oracle. Your
comparison is in fact this: picked = picked='y'.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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