Thread: [BUG] Index operator class + collation = bad SQL

Hi,

I noticed that when using PostgreSQL 9.1 and PgAdmin III 1.14 beta 2,
the operator class, sort order and whether nulls are first or last
appears before collation on a column when showing the index definition
in the SQL pane, but this is invalid syntax.

Test case:

CREATE TABLE dictionary (word text);

CREATE INDEX idx_test
  ON dictionary
  USING btree
  (word COLLATE pg_catalog."zh_CN.utf8" text_pattern_ops DESC NULLS LAST);

Look at the SQL pane for that index and it will produce the following
invalid syntax:

CREATE INDEX idx_test
  ON dictionary
  USING btree
  (word text_pattern_ops DESC NULLS LAST COLLATE pg_catalog."zh_CN.utf8");

Attached a patch to fix.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sun, 2011-07-03 at 15:10 +0100, Thom Brown wrote:
> Hi,
>
> I noticed that when using PostgreSQL 9.1 and PgAdmin III 1.14 beta 2,
> the operator class, sort order and whether nulls are first or last
> appears before collation on a column when showing the index definition
> in the SQL pane, but this is invalid syntax.
>
> Test case:
>
> CREATE TABLE dictionary (word text);
>
> CREATE INDEX idx_test
>   ON dictionary
>   USING btree
>   (word COLLATE pg_catalog."zh_CN.utf8" text_pattern_ops DESC NULLS LAST);
>
> Look at the SQL pane for that index and it will produce the following
> invalid syntax:
>
> CREATE INDEX idx_test
>   ON dictionary
>   USING btree
>   (word text_pattern_ops DESC NULLS LAST COLLATE pg_catalog."zh_CN.utf8");
>
> Attached a patch to fix.
>

You're right. Thanks for your patch. It's now available.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com