PostgreSQL和Unicode表名：当信息表包含Unicode字符时，为什么不能从信息模式中选择表名？ [英] Postgresql and unicode table names: Why can I not select the table name from the information schema when it contains unicode characters?

问题描述

我创建了一个名称中带有Unicode字符的表（以专门测试具有Unicode的表名）。它很好地创建了表，但是我检测表是否存在的方法坏了！

I created a table with a unicode character in the name (to specifically test table names with unicode). It created the table fine, but my method for detecting if the table exists broke!

这里是有问题的交互：

caribou_test=# select table_name from information_schema.tables where table_schema = 'public';
 table_name  
-------------
...
 pinkpink1
(16 rows)

caribou_test=# select table_name from information_schema.tables where table_schema = 'public' and table_name = 'pinkƒpink1';
 table_name 
------------
(0 rows)

caribou_test=# select table_name from information_schema.tables where table_schema = 'public' and table_name = 'pinkpink1';
 table_name 
------------
(0 rows)

caribou_test=# select * from pinkƒpink1;
 id | position | env_id | locked |         created_at         |       updated_at        | status_id | status_position | i1l0  |  f∆   |  growth555   
----+----------+--------+--------+----------------------------+-------------------------+-----------+-----------------+-------+-------+--------------
  1 |        0 |      1 | f      | 2013-06-27 14:50:34.228136 | 2013-06-27 14:50:34.227 |         1 |               0 | YELLL | 55555 | 1.3333388822
(1 row)

表名称为pinkƒpink1（测试数据）。如您所见，当我从information_schema.tables中选择表名时，它显示时没有ƒ，但是我无法以任何一种方式选择表名！但是我仍然可以直接向该表发出选择。

The table name is pinkƒpink1 (test data). As you can see, when I select the table names from information_schema.tables it displays without the ƒ, but I can't select the table name either way! But I can still issue selects to that table directly. What is going on here?

编辑：提供@ craig-ringer所需的信息：

providing requested information for @craig-ringer:

caribou_test=# SELECT current_setting('server_encoding') AS server_encoding, current_setting('client_encoding') AS client_encoding, version();
 server_encoding | client_encoding |                                                                    version                                                                     
-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------
 UTF8            | UTF8            | PostgreSQL 9.2.2 on x86_64-apple-darwin12.2.1, compiled by Apple clang version 4.1 (tags/Apple/clang-421.11.66) (based on LLVM 3.1svn), 64-bit

caribou_test=# SELECT * FROM pg_class WHERE relname = 'pinkƑpink1';
--->  (0 rows)

caribou_test=# SELECT upper('ƒ') = 'Ƒ', lower('Ƒ') = 'ƒ';
 ?column? | ?column? 
----------+----------
 t        | t
(1 row)

caribou_test=# WITH chars(rowid, thechar) AS (VALUES (1,'ƒ'),(2,'Ƒ'),(3,upper('ƒ')),(4,lower('Ƒ'))) SELECT rowid, thechar, convert_to(thechar, 'utf-8') from chars;
 rowid | thechar | convert_to 
-------+---------+------------
     1 | ƒ       | \xc692
     2 | Ƒ       | \xc691
     3 | Ƒ       | \xc691
     4 | ƒ       | \xc692

推荐答案

它看起来像个错误，也许在 regclass 或与此相关的东西：

It looks like a bug, perhaps in regclass or something related to it:

# create table pinkƒpink1 (id serial);
NOTICE:  CREATE TABLE will create implicit sequence "pink?pink1_id_seq" for serial column "pink?pink1.id"
CREATE TABLE
# select 'pinkƒpink1'::name;
    name    
------------
 pinkƒpink1
(1 row)

# select 'pinkƒpink1'::regclass;
  regclass   
-------------
 "pinkpink1"
(1 row)

# select relname from pg_class where oid = 'pinkƒpink1'::regclass;
  relname  
-----------
 pinkpink1

# select relname from pg_class where relname = 'pinkƒpink1'::name;
 relname 
---------
(0 rows)

# select relname from pg_class where relname = 'pinkpink1';
 relname 
---------
(0 rows)

（我的系统是OSX Lion，包含所有utf8，以防万一。）

(My system is OSX Lion with everything utf8, in case it matters.)

，您可以像上面一样（将其找到表格）将其强制转换为 :: regclass 。请注意，如果表不存在，则强制转换为 :: regclass 会产生错误，因此相应地编写代码。

For the workaround, you can cast it to ::regclass as is done above (the one that found the table). Note that casting to ::regclass will yield an error if the table doesn't exist, though, so code around that accordingly.

每个Craig的请求：

Per Craig's request:

# SELECT current_setting('server_encoding') AS server_encoding, current_setting('client_encoding') AS client_encoding, version();
 server_encoding | client_encoding |                                                              version                                                              
-----------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------
 UTF8            | UTF8            | PostgreSQL 9.2.4 on x86_64-apple-darwin11.4.2, compiled by Apple LLVM version 4.2 (clang-425.0.28) (based on LLVM 3.2svn), 64-bit
(1 row)

和每个欧文：

# SELECT name, setting FROM pg_settings WHERE  name IN ('lc_collate','lc_ctype','client_encoding','server_encoding');
      name       |   setting   
-----------------+-------------
 client_encoding | UTF8
 lc_collate      | en_US.UTF-8
 lc_ctype        | en_US.UTF-8
 server_encoding | UTF8
(4 rows)

这篇关于PostgreSQL和Unicode表名：当信息表包含Unicode字符时，为什么不能从信息模式中选择表名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！