Postgres Pro
Downloads
Home   >   mailing lists

Thread: [BUGS] BUG #14704: How to create unique index with a case statement?

[BUGS] BUG #14704: How to create unique index with a case statement?

From
naveen7eceindia@gmail.com
Date:
The following bug has been logged on the website:

Bug reference:      14704
Logged by:          Naveen Vemula
Email address:      naveen7eceindia@gmail.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 0else TDID
end,
case TDLANG when 'ENG' then 0else 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)



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

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

From
Pantelis Theodosiou
Date:


On Tue, Jun 13, 2017 at 6:24 AM, <naveen7eceindia@gmail.com> wrote:
The following bug has been logged on the website:

Bug reference:      14704
Logged by:          Naveen Vemula
Email address:      naveen7eceindia@gmail.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)
  ) ;

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

From
Pantelis Theodosiou
Date:


On Tue, Jun 13, 2017 at 7:18 AM, Pantelis Theodosiou <ypercube@gmail.com> wrote:


On Tue, Jun 13, 2017 at 6:24 AM, <naveen7eceindia@gmail.com> wrote:
The following bug has been logged on the website:

Bug reference:      14704
Logged by:          Naveen Vemula
Email address:      naveen7eceindia@gmail.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.