Collation problem right from AbstractJdbc2DatabaseMetaData.getTables

Getting fresh 9.1devel I found that jdbc driver failed to obtain tables
form the metadata call.

> ResultSet rs = dbMetaData.getTables(null, "%", tableNamePattern == null ? "%" : tableNamePattern, new String[]{"TABLE"});

ERROR: no collation was derived for the sort expression at character
1353
HINT: Use the COLLATE clause to set the collation explicitly.
STATEMENT: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,
c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname =
'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog'
OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM
INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM
TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN
'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END
END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i'
THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c'
THEN 'TYPE' ELSE NULL END ELSE NULL END AS TABLE_TYPE,
d.description AS REMARKS FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid =
d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON
(d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN
pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND (false OR
( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <>
'information_schema' ) ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

Hacking JDBC driver with adding collate "default" doesn't help:

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS
TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname =
'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL
END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i'
THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v'
THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE
c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN
'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' ELSE NULL END
ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON
(dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE
c.relnamespace = n.oid AND (false OR ( c.relkind = 'r' AND n.nspname !
~ '^pg_' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE
collate "C",TABLE_SCHEM,TABLE_NAME ;
ERROR: column "table_type" does not exist
LINE 1: ...D n.nspname <> 'information_schema' ) ) ORDER BY
TABLE_TYPE...

Changing encoding/locale from UTF8 to WIN1252 also doesn't help with the
same result

> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> -----------+----------+----------+-------------+-------------+-----------------------
> postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
> sw2_std | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
> template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
> | | | | | postgres=CTc/postgres
> template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
> | | | | | postgres=CTc/postgres
> (4 rows)

> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> -----------+----------+----------+--------------+--------------+-----------------------
> postgres | postgres | WIN1252 | en_US.CP1252 | en_US.CP1252 |
> sw2_std | postgres | WIN1252 | en_US.CP1252 | en_US.CP1252 |
> template0 | postgres | WIN1252 | en_US.CP1252 | en_US.CP1252 | =c/postgres +
> | | | | | postgres=CTc/postgres
> template1 | postgres | WIN1252 | en_US.CP1252 | en_US.CP1252 | =c/postgres +
> | | | | | postgres=CTc/postgres
> (4 rows)

--
Pavel B. Milovantsev <pav(at)enterprisewizard(dot)com>
EnterpiseWizard