PostgreSQL Oracle/sql server/SYBASE 兼容性之 - nchar , nvarchar , ntext (unicode character)

3 minute read


在Oracle, MS SQL, SYBASE数据库引擎中,有一些这样的字符串类型nchar , nvarchar , ntext。



Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

nchar [ ( n ) ]

Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000.


1、存储的是unicode编码的字符串,使用UNICODE UCS-2字符集。


nchar , nvarchar , ntext 在 PostgreSQL的对应关系

介于以上介绍的两点,只要满足以下条件,char,varchar,text即对应到了nchar , nvarchar , ntext类型。


Name Description Language Server? ICU? Bytes/Char Aliases
UTF8 Unicode, 8-bit all Yes Yes 1-4 Unicode


如果PG 目标DB不是utf8字符集,又当如何


1、实际上依旧可以使用char, varchar, text类型存储(长度限制与上游保持一致即可),只是建议业务方做一下字符集转换后存入PG。

源端存储的 unicode 字符串转换为 PG目标库的目标字符集字符串。

postgres=# \df convert*  
                             List of functions  
   Schema   |     Name     | Result data type | Argument data types | Type   
 pg_catalog | convert      | bytea            | bytea, name, name   | func  
 pg_catalog | convert_from | text             | bytea, name         | func  
 pg_catalog | convert_to   | bytea            | text, name          | func  
(3 rows)  
dbtest1=# select convert_to(N'你好中国','sqlascii');  
(1 row)  
dbtest1=# select convert_to(N'你好中国','utf8');  
(1 row)  

2、或者你在PG中可以使用字节流存储 来自源库unicode 字符串的字节流,读取时再转换为当前字符集。

dbtest1=# \l  
                                 List of databases  
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges     
 dbtest1   | postgres | EUC_CN   | C          | C          |   
create table test (id int, content bytea);  
insert into test values (1, convert_to(N'你好中国','utf8'));  
dbtest1=# select * from test;  
 id |          content             
  1 | \xe4bda0e5a5bde4b8ade59bbd  
(1 row)  
dbtest1=# select convert_from(content,'utf8') from test;  
(1 row)  

PostgreSQL nchar , nvarchar , ntext 的输入语法

N quote

dbtest1=# select N'abc你好中国';  
(1 row)  



 * To ensure that {quotecontinue} can be scanned without having to back up  
 * if the full pattern isn't matched, we include trailing whitespace in  
 * {quotestop}.  This matches all cases where {quotecontinue} fails to match,  
 * except for {quote} followed by whitespace and just one "-" (not two,  
 * which would start a {comment}).  To cover that we have {quotefail}.  
 * The actions for {quotestop} and {quotefail} must throw back characters  
 * beyond the quote proper.  
quote                   '  
quotestop               {quote}{whitespace}*  
quotecontinue   {quote}{whitespace_with_newline}{quote}  
quotefail               {quote}{whitespace}*"-"  
/* Bit string  
 * It is tempting to scan the string for only those characters  
 * which are allowed. However, this leads to silently swallowed  
 * characters if illegal characters are included in the string.  
 * For example, if xbinside is [01] then B'ABCD' is interpreted  
 * as a zero-length string, and the ABCD' is lost!  
 * Better to pass the string forward and let the input routines  
 * validate the contents.  
xbstart                 [bB]{quote}  
xbinside                [^']*  
/* Hexadecimal number */  
xhstart                 [xX]{quote}  
xhinside                [^']*  


/* National character */  
xnstart                 [nN]{quote}  
/* Quoted string that allows backslash escapes */  
xestart                 [eE]{quote}  
xeinside                [^\\']+  
xeescape                [\\][^0-7]  
xeoctesc                [\\][0-7]{1,3}  
xehexesc                [\\]x[0-9A-Fa-f]{1,2}  
xeunicode               [\\](u[0-9A-Fa-f]{4}|U[0-9A-Fa-f]{8})  
xeunicodefail   [\\](u[0-9A-Fa-f]{0,3}|U[0-9A-Fa-f]{0,7})  
/* Extended quote  
 * xqdouble implements embedded quote, ''''  
xqstart                 {quote}  
xqdouble                {quote}{quote}  
xqinside                [^']+  
/* $foo$ style quotes ("dollar quoting")  
 * The quoted string starts with $foo$ where "foo" is an optional string  
 * in the form of an identifier, except that it may not contain "$",  
 * and extends to the first occurrence of an identical string.  
 * There is *no* processing of the quoted text.  
 * {dolqfailed} is an error rule to avoid scanner backup when {dolqdelim}  
 * fails to match its trailing "$".  
dolq_start              [A-Za-z\200-\377_]  
dolq_cont               [A-Za-z\200-\377_0-9]  
dolqdelim               \$({dolq_start}{dolq_cont}*)?\$  
dolqfailed              \${dolq_start}{dolq_cont}*  
dolqinside              [^$]+  
/* Double quote  
 * Allows embedded spaces and other special characters into identifiers.  
dquote                  \"  
xdstart                 {dquote}  
xdstop                  {dquote}  
xddouble                {dquote}{dquote}  
xdinside                [^"]+  
/* Unicode escapes */  
uescape                 [uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}[^']{quote}  
/* error rule to avoid backup */  
uescapefail             [uU][eE][sS][cC][aA][pP][eE]{whitespace}*"-"|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}[^']|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*|[uU][eE][sS][cC][aA][pP]|[uU][eE][sS][cC][aA]|[uU][eE][sS][cC]|[uU][eE][sS]|[uU][eE]|[uU]  
/* Quoted identifier with Unicode escapes */  
xuistart                [uU]&{dquote}  
/* Quoted string with Unicode escapes */  
xusstart                [uU]&{quote}  
/* Optional UESCAPE after a quoted string or identifier with Unicode escapes. */  
xustop1         {uescapefail}?  
xustop2         {uescape}  
/* error rule to avoid backup */  
xufailed                [uU]&  





It’s varchar and text, assuming your database is in UNICODE encoding.

If your database is in a non-UNICODE encoding, there is no special datatype that will give you a unicode string - you can store it as a bytea stream, but that will not be a string.

6、各种数据库引擎(postgresql, mysql, oracle, ms sql, sql lite等)的WEB版SQL在线测试运行环境

7、《PostgreSQL 转义、UNICODE、与SQL注入》

Flag Counter

digoal’s 大量PostgreSQL文章入口