Re: BUG #14704: How to create unique index with a case statement?

On Tue, Jun 13, 2017 at 6:24 AM, <naveen7eceindia(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14704
> Logged by: Naveen Vemula
> Email address: naveen7eceindia(at)gmail(dot)com
> PostgreSQL version: 9.6.0
> Operating system: Windows
> Description:
>
> CREATE UNIQUE INDEX XAK2TRADPAADR_TD ON TRADPAADR_TD(TPID,
> case TDADRTYP when 'BY' then 0
> else TDID
> end,
> case TDADRNO when '1' then 0
> else TDID
> end,
> case TDLANG when 'ENG' then 0
> else 1
> end);
>
> ERROR: syntax error at or near "case"
> LINE 4: case TDADRTYP
> ^
> ********** Error **********
>
> ERROR: syntax error at or near "case"
> SQL state: 42601
> Character: 72
>
> Here TDADRTYP - varchar(4)
> TDID - bigint
> TDADRNO - varchar(15)
> TDLANG - varchar(4)
>
>
>
> You need to put each CASE expression inside parentheses:

CREATE UNIQUE INDEX XAK2TRADPAADR_TD ON TRADPAADR_TD
( TPID,
(case TDADRTYP when 'BY' then 0 else TDID end),
(case TDADRNO when '1' then 0 else TDID end),
(case TDLANG when 'ENG' then 0 else 1 end)
) ;