POSTGRESQL

我不是女神ヾ 2022-01-11 02:05 203阅读 0赞

PostgreSQL中提供了三种实现模式匹配的方法：SQL LIKE操作符，更近一些的SIMILAR TO操作符，和POSIX-风格正则表达式。  
    1. LIKE：  
    string **LIKE** pattern \[ **ESCAPE** escape-character \]  
    string **NOT LIKE** pattern \[ **ESCAPE** escape-character \]  
    每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里，那么LIKE表达式返回真。和我们想象的一样，如果LIKE返回真，那么NOT LIKE表达式返回假，反之亦然。在pattern里的下划线(\_)代表匹配任何单个字符，而一个百分号(%)匹配任何零或更多字符，如：  
*    'abc' LIKE 'abc'     true*  
*    'abc' LIKE 'a%'     true*  
*    'abc' LIKE '\_b\_'    true*  
*    'abc' LIKE 'c'        false  *  
    要匹配文本的下划线或者百分号，而不是匹配其它字符，在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜杠，但是你可以用ESCAPE子句指定一个。要匹配转义字符本身，写两个转义字符。我们也可以通过写成**ESCAPE ''**的方式有效地关闭转义机制，此时，我们就不能关闭下划线和百分号的特殊含义了。  
    关键字**ILIK**E可以用于替换LIKE，令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准，是PostgreSQL的扩展。操作符**~~**等效于**LIKE**， 而**~~\***对应**ILIKE**。还有**!~~**和**!~~\***操作符分别代表**NOT LIKE**和**NOT ILIKE**。所有这些操作符都是PostgreSQL特有的。   
  
    2. SIMILAR TO正则表达式：  
    SIMILAR TO根据模式是否匹配给定的字符串而返回真或者假。  
    string **SIMILAR TO** pattern \[**ESCAPE** escape-character\]  
    string **NOT SIMILAR TO** pattern \[**ESCAPE** escape-character\]  
    它和LIKE非常类似，支持LIKE的通配符(**'\_'**和**'%'**)且保持其原意。除此之外，SIMILAR TO还支持一些自己独有的元字符，如：      
    1). **|** 标识选择(两个候选之一)。  
    2). **\*** 表示重复前面的项零次或更多次。  
    3). **+** 表示重复前面的项一次或更多次。  
    4). 可以使用圆括弧**()**把项组合成一个逻辑项。  
    5). 一个方括弧表达式**\[...\]**声明一个字符表，就像POSIX正则表达式一样。  
    见如下示例：  
*    'abc' SIMILAR TO 'abc'           true*  
*    'abc' SIMILAR TO 'a'              false*  
*    'abc' SIMILAR TO '%(b|d)%'  true*  
*    'abc' SIMILAR TO '(b|c)%'     false*  
    带三个参数的**substring**，substring(string from pattern for escape-character)，提供了一个从字串中抽取一个匹配SQL正则表达式模式的子字串的函数。和SIMILAR TO一样，声明的模式必须匹配整个数据串，否则函数失效并返回NULL。为了标识在成功的时候应该返回的模式部分，模式必须出现后跟双引号(")的两个转义字符。匹配这两个标记之间的模式的字串将被返回，如：  
*    MyTest=\# SELECT substring('foobar' from '%\#"o\_b\#"%' FOR '\#'); --这里\#是转义符，双引号内的模式是返回部分。*  
     substring  
    -----------  
     oob  
    (1 row)  
*    MyTest=\# SELECT substring('foobar' from '\#"o\_b\#"%' FOR '\#');  --foobar不能完全匹配后面的模式，因此返回NULL。*  
     substring  
    -----------  
  
    (1 row)  
  
**七、数据类型格式化函数：**  
  
    PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数</strong></td> 
   <td><strong>返回类型</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
  </tr> 
  <tr> 
   <td>to_char(timestamp, text)</td> 
   <td>text</td> 
   <td>把时间戳转换成字串</td> 
   <td>to_char(current_timestamp, 'HH12:MI:SS')</td> 
  </tr> 
  <tr> 
   <td>to_char(interval, text)</td> 
   <td>text</td> 
   <td>把时间间隔转为字串</td> 
   <td>to_char(interval '15h 2m 12s', 'HH24:MI:SS')</td> 
  </tr> 
  <tr> 
   <td>to_char(int, text)</td> 
   <td>text</td> 
   <td>把整数转换成字串</td> 
   <td>to_char(125, '999')</td> 
  </tr> 
  <tr> 
   <td>to_char(double precision, text)</td> 
   <td>text</td> 
   <td>把实数/双精度数转换成字串</td> 
   <td>to_char(125.8::real, '999D9')</td> 
  </tr> 
  <tr> 
   <td>to_char(numeric, text)</td> 
   <td>text</td> 
   <td>把numeric转换成字串</td> 
   <td>to_char(-125.8, '999D99S')</td> 
  </tr> 
  <tr> 
   <td>to_date(text, text)</td> 
   <td>date</td> 
   <td>把字串转换成日期</td> 
   <td>to_date('05 Dec 2000', 'DD Mon YYYY')</td> 
  </tr> 
  <tr> 
   <td>to_timestamp(text, text)</td> 
   <td>timestamp</td> 
   <td>把字串转换成时间戳</td> 
   <td>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</td> 
  </tr> 
  <tr> 
   <td>to_timestamp(double)</td> 
   <td>timestamp</td> 
   <td>把UNIX纪元转换成时间戳</td> 
   <td>to_timestamp(200120400)</td> 
  </tr> 
  <tr> 
   <td>to_number(text, text)</td> 
   <td>numeric</td> 
   <td>把字串转换成numeric</td> 
   <td>to_number('12,454.8-', '99G999D9S')</td> 
  </tr> 
 </tbody> 
</table>

1. 用于日期/时间格式化的模式：

<table> 
 <tbody> 
  <tr> 
   <td><strong>模式</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td>HH</td> 
   <td>一天的小时数(01-12)</td> 
  </tr> 
  <tr> 
   <td>HH12</td> 
   <td>一天的小时数(01-12)</td> 
  </tr> 
  <tr> 
   <td>HH24</td> 
   <td>一天的小时数(00-23)</td> 
  </tr> 
  <tr> 
   <td>MI</td> 
   <td>分钟(00-59)</td> 
  </tr> 
  <tr> 
   <td>SS</td> 
   <td>秒(00-59)</td> 
  </tr> 
  <tr> 
   <td>MS</td> 
   <td>毫秒(000-999)</td> 
  </tr> 
  <tr> 
   <td>US</td> 
   <td>微秒(000000-999999)</td> 
  </tr> 
  <tr> 
   <td>AM</td> 
   <td>正午标识(大写)</td> 
  </tr> 
  <tr> 
   <td>Y,YYY</td> 
   <td>带逗号的年(4和更多位)</td> 
  </tr> 
  <tr> 
   <td>YYYY</td> 
   <td>年(4和更多位)</td> 
  </tr> 
  <tr> 
   <td>YYY</td> 
   <td>年的后三位</td> 
  </tr> 
  <tr> 
   <td>YY</td> 
   <td>年的后两位</td> 
  </tr> 
  <tr> 
   <td>Y</td> 
   <td>年的最后一位</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>全长大写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>Month</td> 
   <td>全长混合大小写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>month</td> 
   <td>全长小写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>MON</td> 
   <td>大写缩写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>Mon</td> 
   <td>缩写混合大小写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>mon</td> 
   <td>小写缩写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>MM</td> 
   <td>月份号(01-12)</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>全长大写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>Day</td> 
   <td>全长混合大小写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>day</td> 
   <td>全长小写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>DY</td> 
   <td>缩写大写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>Dy</td> 
   <td>缩写混合大小写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>dy</td> 
   <td>缩写小写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>DDD</td> 
   <td>一年里的日子(001-366)</td> 
  </tr> 
  <tr> 
   <td>DD</td> 
   <td>一个月里的日子(01-31)</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>一周里的日子(1-7；周日是1)</td> 
  </tr> 
  <tr> 
   <td>W</td> 
   <td>一个月里的周数(1-5)(第一周从该月第一天开始)</td> 
  </tr> 
  <tr> 
   <td>WW</td> 
   <td>一年里的周数(1-53)(第一周从该年的第一天开始)</td> 
  </tr> 
 </tbody> 
</table>

2. 用于数值格式化的模板模式：

<table> 
 <tbody> 
  <tr> 
   <td><strong>模式</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>带有指定数值位数的值</td> 
  </tr> 
  <tr> 
   <td>0</td> 
   <td>带前导零的值</td> 
  </tr> 
  <tr> 
   <td>.(句点)</td> 
   <td>小数点</td> 
  </tr> 
  <tr> 
   <td>,(逗号)</td> 
   <td>分组(千)分隔符</td> 
  </tr> 
  <tr> 
   <td>PR</td> 
   <td>尖括号内负值</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>带符号的数值</td> 
  </tr> 
  <tr> 
   <td>L</td> 
   <td>货币符号</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>小数点</td> 
  </tr> 
  <tr> 
   <td>G</td> 
   <td>分组分隔符</td> 
  </tr> 
  <tr> 
   <td>MI</td> 
   <td>在指明的位置的负号(如果数字 &lt; 0)</td> 
  </tr> 
  <tr> 
   <td>PL</td> 
   <td>在指明的位置的正号(如果数字 &gt; 0)</td> 
  </tr> 
  <tr> 
   <td>SG</td> 
   <td>在指明的位置的正/负号</td> 
  </tr> 
 </tbody> 
</table>

**八、时间/日期函数和操作符：**  
  
    1. 下面是PostgreSQL中支持的时间/日期操作符的列表：

<table> 
 <tbody> 
  <tr> 
   <td><strong>操作符</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + integer '7'</td> 
   <td>date '2001-10-05'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + interval '1 hour'</td> 
   <td>timestamp '2001-09-28 01:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + time '03:00'</td> 
   <td>timestamp '2001-09-28 03:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>interval '1 day' + interval '1 hour'</td> 
   <td>interval '1 day 01:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>timestamp '2001-09-28 01:00' + interval '23 hours'</td> 
   <td>timestamp '2001-09-29 00:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>time '01:00' + interval '3 hours'</td> 
   <td>time '04:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>- interval '23 hours'</td> 
   <td>interval '-23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-10-01' - date '2001-09-28'</td> 
   <td>integer '3'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-10-01' - integer '7'</td> 
   <td>date '2001-09-24'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-09-28' - interval '1 hour'</td> 
   <td>timestamp '2001-09-27 23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>time '05:00' - time '03:00'</td> 
   <td>interval '02:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>time '05:00' - interval '2 hours'</td> 
   <td>time '03:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>timestamp '2001-09-28 23:00' - interval '23 hours'</td> 
   <td>timestamp '2001-09-28 00:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>interval '1 day' - interval '1 hour'</td> 
   <td>interval '23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</td> 
   <td>interval '1 day 15:00'</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>interval '1 hour' * double precision '3.5'</td> 
   <td>interval '03:30'</td> 
  </tr> 
  <tr> 
   <td>/</td> 
   <td>interval '1 hour' / double precision '1.5'</td> 
   <td>interval '00:40'</td> 
  </tr> 
 </tbody> 
</table>

2. 日期/时间函数：

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数</strong></td> 
   <td><strong>返回类型</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>age(timestamp, timestamp)</td> 
   <td>interval</td> 
   <td>减去参数，生成一个使用年、月的"符号化"的结果</td> 
   <td>age('2001-04-10', timestamp '1957-06-13')</td> 
   <td>43 years 9 mons 27 days</td> 
  </tr> 
  <tr> 
   <td>age(timestamp)</td> 
   <td>interval</td> 
   <td>从current_date减去得到的数值</td> 
   <td>age(timestamp '1957-06-13')</td> 
   <td>43 years 8 mons 3 days</td> 
  </tr> 
  <tr> 
   <td>current_date</td> 
   <td>date</td> 
   <td>今天的日期</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>current_time</td> 
   <td>time</td> 
   <td>现在的时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>current_timestamp</td> 
   <td>timestamp</td> 
   <td>日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>date_part(text, timestamp)</td> 
   <td>double</td> 
   <td>获取子域(等效于extract)</td> 
   <td>date_part('hour', timestamp '2001-02-16 20:38:40')</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>date_part(text, interval)</td> 
   <td>double</td> 
   <td>获取子域(等效于extract)</td> 
   <td>date_part('month', interval '2 years 3 months')</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>date_trunc(text, timestamp)</td> 
   <td>timestamp</td> 
   <td>截断成指定的精度</td> 
   <td>date_trunc('hour', timestamp '2001-02-16 20:38:40')</td> 
   <td>2001-02-16 20:00:00+00</td> 
  </tr> 
  <tr> 
   <td>extract(field from timestamp)</td> 
   <td>double</td> 
   <td>获取子域</td> 
   <td>extract(hour from timestamp '2001-02-16 20:38:40')</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>extract(field from interval)</td> 
   <td>double</td> 
   <td>获取子域</td> 
   <td>extract(month from interval '2 years 3 months')</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>localtime</td> 
   <td>time</td> 
   <td>今日的时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>localtimestamp</td> 
   <td>timestamp</td> 
   <td>日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>timestamp</td> 
   <td>当前的日期和时间(等效于 current_timestamp)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>timeofday()</td> 
   <td>text</td> 
   <td>当前日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

3. EXTRACT，date\_part函数支持的field：

<table> 
 <tbody> 
  <tr> 
   <td><strong>域</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>CENTURY</td> 
   <td>世纪</td> 
   <td>EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>(月分)里的日期域(1-31)</td> 
   <td>EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>16</td> 
  </tr> 
  <tr> 
   <td>DECADE</td> 
   <td>年份域除以10</td> 
   <td>EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>DOW</td> 
   <td>每周的星期号(0-6；星期天是0) (仅用于timestamp)</td> 
   <td>EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>DOY</td> 
   <td>一年的第几天(1 -365/366) (仅用于 timestamp)</td> 
   <td>EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>47</td> 
  </tr> 
  <tr> 
   <td>HOUR</td> 
   <td>小时域(0-23)</td> 
   <td>EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>MICROSECONDS</td> 
   <td>秒域，包括小数部分，乘以 1,000,000。</td> 
   <td>EXTRACT(MICROSECONDS from TIME '17:12:28.5');</td> 
   <td>28500000</td> 
  </tr> 
  <tr> 
   <td>MILLENNIUM</td> 
   <td>千年</td> 
   <td>EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>MILLISECONDS</td> 
   <td>秒域，包括小数部分，乘以 1000。</td> 
   <td>EXTRACT(MILLISECONDS from TIME '17:12:28.5');</td> 
   <td>28500</td> 
  </tr> 
  <tr> 
   <td>MINUTE</td> 
   <td>分钟域(0-59)</td> 
   <td>EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>38</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11)</td> 
   <td>EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>QUARTER</td> 
   <td>该天所在的该年的季度(1-4)(仅用于 timestamp)</td> 
   <td>EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>SECOND</td> 
   <td>秒域，包括小数部分(0-59[1])</td> 
   <td>EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>40</td> 
  </tr> 
  <tr> 
   <td>WEEK</td> 
   <td>该天在所在的年份里是第几周。</td> 
   <td>EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>年份域</td> 
   <td>EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>2001</td> 
  </tr> 
 </tbody> 
</table>

4. 当前日期/时间：  
    我们可以使用下面的函数获取当前的日期和/或时间∶  
    CURRENT\_DATE  
    CURRENT\_TIME  
    CURRENT\_TIMESTAMP  
    CURRENT\_TIME (precision)  
    CURRENT\_TIMESTAMP (precision)  
    LOCALTIME  
    LOCALTIMESTAMP  
    LOCALTIME (precision)  
    LOCALTIMESTAMP (precision)

*    该博客中提供的所有信息均源自PostgreSQL官方文档，编写该篇博客的主要目的是便于今后的查阅，特此声明。*

创建序列号:

*CREATE SEQUENCE public.id\_seq  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 9223372036854775807  
    START 100000000001  
    CACHE 1  
    CYCLE;*

*-- Permissions*

*ALTER SEQUENCE public.id\_seq OWNER TO postgres;  
GRANT ALL ON SEQUENCE public.id\_seq TO postgres;*

*--主键自增生成  
ALTER TABLE public.table ALTER COLUMN id SET DEFAULT to\_number(CONCAT(to\_char(now(),'YYMMDD'),nextval('id\_seq')),'999999999999999999');*

**九. 数值类型**

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>存储空间</th> 
   <th>描述</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">smallint</td> 
   <td style="vertical-align:top;">2 字节</td> 
   <td style="vertical-align:top;">小范围整数</td> 
   <td style="vertical-align:top;">-32768 到 +32767</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">integer</td> 
   <td style="vertical-align:top;">4 字节</td> 
   <td style="vertical-align:top;">常用的整数</td> 
   <td style="vertical-align:top;">-2147483648 到 +2147483647</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bigint</td> 
   <td style="vertical-align:top;">8 字节</td> 
   <td style="vertical-align:top;">大范围的整数</td> 
   <td style="vertical-align:top;">-9223372036854775808 到 9223372036854775807</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">decimal</td> 
   <td style="vertical-align:top;">变长</td> 
   <td style="vertical-align:top;">用户声明精度，精确</td> 
   <td style="vertical-align:top;">无限制</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">numeric</td> 
   <td style="vertical-align:top;">变长</td> 
   <td style="vertical-align:top;">用户声明精度，精确</td> 
   <td style="vertical-align:top;">无限制</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">real</td> 
   <td style="vertical-align:top;">4 字节</td> 
   <td style="vertical-align:top;">变精度，不精确</td> 
   <td style="vertical-align:top;">6 位十进制数字精度</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">double precision</td> 
   <td style="vertical-align:top;">8 字节</td> 
   <td style="vertical-align:top;">变精度，不精确</td> 
   <td style="vertical-align:top;">15 位十进制数字精度</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">serial</td> 
   <td style="vertical-align:top;">4 字节</td> 
   <td style="vertical-align:top;">自增整数</td> 
   <td style="vertical-align:top;">1 到 +2147483647</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bigserial</td> 
   <td style="vertical-align:top;">8 字节</td> 
   <td style="vertical-align:top;">大范围的自增整数</td> 
   <td style="vertical-align:top;">1 到 9223372036854775807</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>名字</th> 
   <th>别名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">bigint</td> 
   <td style="vertical-align:top;">int8</td> 
   <td style="vertical-align:top;">有符号 8 字节整数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bigserial</td> 
   <td style="vertical-align:top;">serial8</td> 
   <td style="vertical-align:top;">自增八字节整数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bit [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">定长位串</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bit varying [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">varbit</td> 
   <td style="vertical-align:top;">变长位串</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">boolean</td> 
   <td style="vertical-align:top;">bool</td> 
   <td style="vertical-align:top;">逻辑布尔量 （真/假）</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">box</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的长方形</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">bytea</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">二进制数据（"字节数组"）</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">character varying [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">varchar [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">变长字符串</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">character [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">char [ (<em>n</em>) ]</td> 
   <td style="vertical-align:top;">定长字符串</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">cidr</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">IPv4 或者 IPv6 网络地址</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">circle</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的圆</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">date</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">日历日期（年，月，日）</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">double precision</td> 
   <td style="vertical-align:top;">float8</td> 
   <td style="vertical-align:top;">双精度浮点数字</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">inet</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">IPv4 或者 IPv6 网络地址</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">integer</td> 
   <td style="vertical-align:top;">int，int4</td> 
   <td style="vertical-align:top;">四字节长有符号整数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">interval [ (<em>p</em>) ]</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">时间间隔</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">line</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的无限长直线</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">lseg</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的线段</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">macaddr</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">MAC 地址</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">money</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">货币金额</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">numeric [ (<em>p</em>,&nbsp;<em>s</em>) ]</td> 
   <td style="vertical-align:top;">decimal [ (<em>p</em>,&nbsp;<em>s</em>) ]</td> 
   <td style="vertical-align:top;">可选精度的准确数字</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">path</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的几何路径</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">point</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的点</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">polygon</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">平面中的封闭几何路径</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">real</td> 
   <td style="vertical-align:top;">float4</td> 
   <td style="vertical-align:top;">单精度浮点数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">smallint</td> 
   <td style="vertical-align:top;">int2</td> 
   <td style="vertical-align:top;">有符号两字节整数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">serial</td> 
   <td style="vertical-align:top;">serial4</td> 
   <td style="vertical-align:top;">自增四字节整数</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">text</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">变长字符串</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">time [ (<em>p</em>) ] [ without time zone ]</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">一天里的时间</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">time [ (<em>p</em>) ] with time zone</td> 
   <td style="vertical-align:top;">timetz</td> 
   <td style="vertical-align:top;">一天里的时间，包括时区</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">timestamp [ (<em>p</em>) ] [ without time zone ]</td> 
   <td style="vertical-align:top;">&nbsp;</td> 
   <td style="vertical-align:top;">日期和时间</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;">timestamp [ (<em>p</em>) ] with time zone</td> 
   <td style="vertical-align:top;">timestamptz</td> 
   <td style="vertical-align:top;">日期和时间</td> 
  </tr> 
 </tbody> 
</table>