to pg

Surely just

CASE picked WHEN 'y' THEN load_id ELSE NULL END

or

CASE WHEN picked='y' THEN load_id ELSE NULL END

?

On 25 September 2015 at 12:08, Ramesh T <rameshparnanditech@gmail.com>
wrote:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ramesh T
Sent: Friday, September 25, 2015 7:09 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] to pg

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...

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

Regards,
Igor Neyman

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general