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

On Tue, Jun 13, 2017 at 7:18 AM, Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> 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)
> ) ;
>
>
As the docs state in CREATE INDEX
https://www.postgresql.org/docs/current/static/sql-createindex.html :

> The key field(s) for the index are specified as column names, or
alternatively as expressions written in parentheses.