数据类型介绍
数据类型的分类
布尔类型
布尔类型介绍
boolean的值要么是true（真），要么是false（假），如果是unknown（未知）状态，） 用NULL表示。boolean在SQL中可以用不带引号的TRUE或FALSE表示，也可以用其他表示“真”和“假”的带引号字符表示，如‘true‘、‘false‘、yes、‘no‘，等等。
布尔类型的操作符
布尔类型可以使用的操作符是逻辑操作符和比较操作符。
布尔AND、OR运算真值表：
NOT运行真值表：
常用的逻辑操作符有：AND、OR、NOT。
SQL使用三值的布尔逻辑：TRUE、FALSE和NULL，其中NULL代表“未知”。运算规则见表5-2和表5-3。
操作符AND和OR左右两边的操作是可以互相交换的，也就是说， “a AND b”结果与 “b AND a”的结果是相同的。
数值类型
数值类型介绍
整数类型
整数类型有3种：smallint、 int、bigint，注意，PostgreSQL中没有MySQL中的 tinyint（1字节）、mediumint（3字节）这两种类型，也没有MySQL中的unsigned类型。
常用的数据类型是int（或integer），因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint类型。通常，只有integer类型的取值范围不够时才使用bigint类型，因为前者的执行速度绝对快得多。
SQL只声明了整数类型integer（或int）和smallint。int与integer和int4是等效的， int2与smallint是等效的，bigint与int8是等效的。
准确的小数类型
精确的小数类型可用numeric、numeric（m，n）、numeric（m）表示。
其中，numeric类型和decimal类型是等效的，这两种类型都是SQL标准，可以存储最多1000位精度的数字，并且可准确地进行计算。它们特别适用于货币金额和其他要求精确计算的场合。不过，基于numeric类型的算术运算相比于基于整数类型或者下面介绍的浮点数类型的算术运算，其速度要慢很多。
如果要声明一个字段的类型为numeric，可以用下面的语句：
NUMERIC（precision， scale）
其中，精度precision必须为正数，标度scale可以为0或者正数。
NUMERIC（precision）表示标度为0，与NUMERIC（precision，O）的含义是相同的。
如果不带任何精度与标度地声明NUMERIC，则表示创建一个可以存储任意精度和标度的数值（当然不能超过系统可以实现的精度和标度），这种类型的字段不会把输入数值转化成任何特定的标度，而带有标度声明的NUMERIC字段会把输入值转化为指定标度。在标准SQL和MySQL中，语法DECIMAL等价于DECIMAL（M，O），M在MySQL中默认为10，PostgreSQL中因作用不大而把它改成了一个任意精度和标度的数值。如果你关心可移植性，建议总是明确声明精度和标度。代码如下：
osdba=# create table t（id1 numeric （3），jd2 numeric（3，0），id3 numeric（3，2），id4 numeric）；
若字段声明了标度，超过小数点位数的标度会自动4舍5 入后进行存储。而对于既没有声明精度也没有声明标度的number类型来说，则会原样存储。
对于声明了精度的数值，如果INSERT语句插入的数值超出声明的精度范围，则会报错。
浮点数类型
数据类型real和double precision是不精确的、变精度的数字类型。
对于浮点数，需要注意如下几个方面：
•如果要求精确地计算（比如计算货币金额），应使用numeric类型。
•如果想用这些类型做任何重要的复杂计算，尤其是那些对范围情况（无穷/下溢） 严重依赖的复杂计算，那么应该仔细评诂你的实现。
•对两个浮点数值进行相等性比较时，有可能不会像你所想象的那样运转。 除了普通的数字值之外，浮点类型还有以下几个特殊值：
•Infinity。
•-Infinity。
•NaN。
这些值分别表示特殊值“正无穷大”“负无穷大”“不是一个数字”。在不遵循IEEE 754 浮点算术的机器上，这些值的含义可能不是预期的。如果在SQL命令里把这些数值当作常量来写，必须在它们周围放上单引号，如“UPDATE table SET x=Infinity”。输入时， 这些值与大小写无关。
序列类型
在序列类型中，serial和bigserial与MySQL中的自增字段含义相同。PostgreSQL实际上是通过序列（sequence）实现的。PostgreSQL数据库与Oracle一样有序列，而 MySQL中没有序列。示例如下：
CREATE TABLE t（
Id SERIAL
）；
上面的语句等价于声明下面几个语句：
CREATE SEQUENCE t_id_seq；
CREATE TABLE t（
id integer NOT NULL DEFAULT nextval（‘t_id_seg’）
）；
ALTER SEQUENCE t_id_seq OWNED BY t.id；
货币类型
货币类型可以存储固定小数的货币数目，与浮点数不同，它是完全保证精度的。其输出格式与参数lc_monetary的设置有关，不同的国家其货币输出格式也不相同，示例如下：
osdba=#SELECT‘12.34：：money；
如果是中文，输出的是“¥12.34”，如果是英文（美国），则输出为“$12.34”。
money类型占用8字节空间来存储数据，表示的范围为-92233720368547758.08 到+92233720368547758.07。
数学函数和操作符
数学操作符：
数学函数：
数学函数之三角函数：
其中，“dp”表示double precision。 所列函数中，许多都有多种不同的形式，不同形式的区别在于参数不同。除非特别指明，任何特定形式的函数都会返回和它的参数相同的数据类型。处理double precision数据的函数大多是在宿主系统的C库的基础上实现的。因此，精度和数值范围方面的行为都是根据宿主系统的不同而变化的。
字符串类型
字符串类型介绍
varchar（n）和char（n）分别是character varying（n）和character（n）的别名，未声明长度的character等价于character（1）；如果使用character varying时不带长度说明词，那么该类型接受任何长度的字符串。不带长度说明词是PostgreSQL的扩展，其他数据库中一般不能这样使用。
这些类型的存储长度是4字节加上实际的字符串长度，比如，character的存储长度为4+n，n为定义时的长度。长的字符串会被系统自动压缩，因此在磁盘上的物理长度可能会更小些。长的内容也可能会存储在toast表中，这里只放一个指针，这样它们就不会干扰对短字段值的快速访问了。不管怎样，允许存储的最长字符串大概是1GB。
虽然在某些其他的数据库系统里，定长的character（n）有一定的性能优势，但在 PostgreSQL中， 定长的character（n）与varchar（n）没有差别。故在大多数情况下，建议使用text或varchar。
字符串函数和操作符
除了上面表中所列的字符串和操作符以外，还有其他的字符串操作函数可以使用，其中有些用于在内部实现表中列出的SQL标准字符串函数。
二进制数据类型
二进制数据类型介绍
PostgreSQL中只有一种二进制类型：bytea类型。此数据类型允许存储二进制字符串，对应MySQL和Oracle中的blob类型。Oracle中的raw类型也可以用该类型取代。
二进制字符串是一个字节序列。二进制字符串和普通字符串的区别有两个：第一， 二进制字符串完全可以存储字节零值，以及其他“不可打印”的字节（定义在32到126范围之外的字节）。普通字符串不允许字节零值，而且也不允许存储那些不符合选定的字符集编码的非法字节值或者字节序列。第二，对二进制字符串的处理实际上就是对字节的处理，而对字符串的处理，则取决于区域设置。简单说，二进制字符串适于存储那些程序员认为是“原始字节”的数据，比如图片内容，而字符串则适合存储文本。
二进制数据类型转义表示
既然二进制字符串中的部分字符为不可打印字符，那么如何在SQL语句的文本串中输入bytea数值呢？答案是使用转义。通常来说，要转义一个字节值，需要把它的数值转换成对应的三位八进制数，并且加两个前导反斜杠。有些八进制数值可以加一个反斜杠直接转义，比如单引号和反斜杠本身。
二进制数据类型的函数
位串类型
位串类型介绍
位串就是一串由1和0组成的字符串。PostgreSQL中可以直观地显式操作二进制位。
下面是两种SQL位类型：
•bit（n）。
• bit varying（n）。
其中n是一个正整数。
bit（n）类型的数据必须准确匹配长度n，试图存储短一些或者长一些的数据都是错误的。
bit varying（n）类型的数据是最长为n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于bit（1），没有长度的bit varying表示没有长度限制。
如果明确地把一个位串值转换成bit（n），那么它的右边将被截断，或者在右边补齐0 到刚好为n位，而不会抛出任何错误。类似地，如果明确地把一个位串数值转换成bit varying（n），而其超过n位，那么它的右边将被截断。
位串类型的使用方法
下面介绍位串类型的使用方法，首先创建一个测试表，示例如下：
CREATE TABLE test （a BIT（3）， b BIT VARYING（5））；
插入一条测试数据：
osdba=# INSERT INTO test VALUES （B‘101， B‘00“；
INSERT 01
对于bit（n）字段，如果插入的数据的长度小于定义的长度n或超过了定义的长度n都将报错：
osdba=# INSERT INTO test VALUES （B“10‘， B“101）；
ERROR： bit string length 2 does not match type bit（3） osdba=# INSERT INTO test VALUES （B‘11110‘， B‘101）；
ERROR： bit string length 5 does not match type bit（3）
对于bit varying（n），如果插入的数据超过了匹配的长度n也会报错：
osdba=# INSERT INTO test VALUES （B“110， B“111101‘）；
ERROR： bit string too long for type bit varying（5）
位串类型的操作符及函数
位串除了常用的比较操作符之外，主要支持一些位运算的操作符。
下列SQL标准函数既可以用于字符串，也可以用于位串：
•length。
•bit_length。
•octet_length。
•position。
•substring。
•overlay。
下列函数既支持二进制字符串，也可用于位串：
•get_bit。
•set_bit。
当用于位串时，上述函数的位数将以串（最左边）的第一位作为0位。
另外，可以在整数和bit之间进行转换。示例如下：
osdba=# select 66：：bit（10）；
下面来看一下PostgreSQL中十进制、十六进制、二进制之间的转换示例。
十进制转二进制的示例如下：
osdba=# select 85：bit（8）；
十六进制转二进制的示例如下：
osdba=# select‘xff‘：：bit（8）；
十六进制转十进制的示例如下：
osdba=# select‘xff‘：：bit（8）：int；
十进制转十六进制的示例如下：
osdba=# select to_hex（255）；
日期/时间类型
日期/时间类型介绍
需要注意的是，PostgreSQL中的时间类型可以精确到秒以下，而MySQL中的时间类型只能精确到秒。time、timestamp、interval接受一个可选的精度值p以指明秒域中小数部分的位数。如果没有明确的默认精度，对于timestamp和interval类型，p的取值范围是0～6。
timestamp数值是以双精度浮点数的方式存储的，它以2000-01-01午夜之前或之后的秒数存储。可以想象，在2000-01-01前后几年的日期中精度是可以达到微秒的，而在更远一些的日子，精度可能达不到微秒，但达到毫秒是没有问题的。
也可以改变编译选项使timestamp以八字节整数的方式存储，那么微秒的精度就可以在数值的全部范围内得到保证，不过这样一来八位整数的时间戳范围就缩小到了4713 BC到294276 AD之间。此外，这个编译选项也决定了time和interval数值是保存成浮点数还是八字节整数。同样，在以浮点数存储的时候，随着时间间隔的增加，interval数值的精度也会降低。
日期输入
在SQL中，任何日期或者时间的文本输入都需要由“日期/时间”类型加单引号括起来的字符串组成，语法如下：
type ［ （p）］‘value‘
日期和时间的输入几乎可以是任何合理的格式，包括ISO-8601格式、SQL-兼容格式、传统的Postgres格式及其他形式。对于一些格式，日期输入中的月和日可能会使人产生疑惑，因此系统支持自定义这些字段的顺序。如果DateStyle参数默认为“MDY” 则表示按“月-日-年”的格式进行解析，如果参数设置为“DMY”，则按照“日-月一年”的格式进行解析，设置为，“YMD”表示按照“年-月-日”的格式进行解析。示例如下；
osdba=# create table t（coll date）；
CREATE TABLE
osdba=# insert into t values（date ‘12-10-2010“）；
对于中国人来说，使用“/”作为时间和日期分隔符容易产生歧义，最好使用“_”，然后以“年一月一日”的格式输入日期。
时间输入
输入时间时需要注意时区的输入。time被认为是time without time zone的类型， 这样即使字符串中有时区也会被忽略，示例如下：
osdba=# select time ‘04：05：06‘；
时间字符串可以使用冒号作分隔符，即输入格式为“hh：mm：ss”，如“10：23：45”，也可以不用分隔符，如“102345”表示10点23分45秒。
最好不要用时区缩写来表示时区，因为这样有可能给阅读者带来困扰，如CST 时间有可能有以下几种含义：
• Central Standard Time （USA） UT-6：00， 即美国标准时间。
• Central Standard Time （Australia） UT+9：30，即澳大利亚标准时间。
• China Standard Time UT+8：00，即中国标准时间。
•Cuba Standard Time UT-4：00，即古巴标准时间。
这么多的时区都叫CST，是不是让人困惑？CST在PostgreSQL中代表Central Standard Time （USA） UT-6：00， 缩写可以查询视图“pg_timezone_abbrevs”：
osdba=# select * from pg_timezone_abbrevs where abbrev=‘CST‘；
在输入的时间后加“AT TIME ZONE”可以转换或指定时区：
osdba=# SELECT TIMESTAMP WITH TIME ZONE‘2001-02-16 20：38：40-05‘ AT TIME ZONE‘+08：00‘；
特殊值
为方便起见，PostgreSQL中用了一些特殊的字符串输入值表示特定的意义。
函数和操作符列表
日期、时间和inteval类型数值之间可以进行加减乘除运算。
日期、时间和inteval类型的函数。
除了以上函数以外，PostgreSQL还支持SQL的OVERLAPS操作符，如下：
（start1， end1） OVERLAPS （start2， end2）
（start1， length1） OVERLAPS （start2， length2）
上面的表达式在两个时间域（用它们的终点定义）重叠的时候生成真值。终点可以用一对日期、时间、时间戳来声明；或者是后面跟着一个表示时间间隔的日期、时间、 时间戳，示例如下：
SELECT （DATE “2001-02-16‘， DATE ‘2001-12-21） OVERLAPS （DATE‘2001-10-30‘，DATE‘2002-10-30‘）；
Result： true
SELECT （DATE‘2001-02-16‘， INTERVAL ‘100 days“） OVERLAPS （DATE‘2001-10-30‘，DATE“2002-10-30‘；
Result： false
时间函数
PostgreSQL提供了许多用于返回当前日期和时间的函数。下面的函数都是按照当前事务开始的时间返回结果的：
•CURRENT_DATE。
•CURRENT_TIME。
•CURRENT_TIMESTAMP。
•CURRENT_TIME（precision）。
•CURRENT_TIMESTAMP（precision）。
•LOCALTIME。
•LOCALTIMESTAMP。
•LOCALTIME（precision）。
•LOCALTIMESTAMP（precision）。
•now（）。
•transaction_timestamp（）。
其中，CURRENT_TIME和CURRENT_TIMESTAMP返回带时区的值；LOCALTIME 和LOCALTIMESTAMP返回不带时区的值。
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMES- TAMP可以选择性地给予一个精度参数，该精度会导致结果的秒数域被四舍五入到指定的小数位。如果没有精度参数，将给予所能得到的全部精度。
因为这些函数全部是按照当前事务开始的时间返回结果的，所以它们的值在整个事务运行期间都不会改变。PostgreSQL这样做是为了允许一个事务在“当前时间”上有连贯的概念，这样同一个事务里的多个修改就可以保持同样的时间戳了。
PostgreSQL同样也提供了返回实时时间值的函数，它们的返回值会在事务中随时间的推移而不断变化。这些函数列表如下：
•statement_timestamp（）。
• clock_timestamp（）。
•timeofday（）。
now（）函数、CURRENT_TIMESTAMP函数和transaction_timestamp（）函数是等效的。不过，transaction_timestamp（）的命名更准确地表明了其含义。statement _timestamp（返回当前语句开始时刻的时间戳。statement_timestamp（）和transaction_timestamp（）在一个事务的第一条命令里的返回值相同，但是在随后的命令中返回结果却不一定相同。clock_timestamp（）返回实时时钟的当前时间戳，因此它的值甚至在同一条SQL 命令中都会变化。timeofday（/相当于clock_timestamp（），也返回实时时钟的当前时间戳，但它返回的是一个text字符串，而不是timestamp with time zone值。
所有日期/时间类型还接受特殊的文本值“now”，用于声明当前的日期和时间（重申：乃当前事务开始的时间）。因此，下面3个语句都返回相同的结果：
•SELECT CURRENT_TIMESTAMP。
•SELECT now().
•SELECT TIMESTAMP with time zone 'now.
extract和date_part函数
extract函数格式如下：
extract （field FROM source）
extract函数从日期/时间数值中抽取子域，比如年、小时等，其返回类型为double precision的数值。source必须是一个timestamp或time或interval类型的值表达式，此外，类型为date的表达式可自动转换为timestamp，因此source也可以用date类型。
field是一个标识符或者字符串，它指定从源数据中抽取的域。表5-20中列出了field可以取的各类值及示例。
枚举类型
枚举类型是包含一系列有序的静态值集合的一个数据类型，等于某些编程语言中的 enum类型。
枚举类型的使用
与MySQL不一样，在PostgreSQL中要使用枚举类型需要先使用CREATE TYPE来创建此枚举类型。示例如下。
先建一个名为“week”的枚举类型，并建一张测试表：
CREATE TYPE week AS ENUM （Sun‘， Mon， Tues， Wed， Thur， Fri， Sat）；
CREATE TABLE dutylperson text， weekday week）；
INSERT INTO duty values（“张三‘ ，‘Sun‘）；
INSERT INTO duty values（李四，Mon‘)；
INSERT INTO duty values（‘王二，‘Tues“)；
INSERT INTO duty values（赵五，Wed‘）；
试着查询一条数据：
osdba=# SELECT * FROM duty WHERE weekday =‘Sun‘；
如果输入的字符串不在枚举类型之间，则会报错：
osdba=# SELECT * FROM duty WHERE weekday = ‘Sun。‘；
ERROR： invalid input value for enum week：“Sun。“ LINE 1： SELECT * FROM duty WHERE weekday =‘Sun。‘；
在psqI中可以使用“\dT”命令查看枚举类型的定义：
osdba=#\dT+ week
直接查询表“pg_enum”也可以看到枚举类型的定义：
osdba=# select * from pg_enum；
枚举类型说明
在枚举类型中，值的顺序是创建枚举类型时定义的顺序。所有的比较标准运算符及相关的聚集函数都可支持枚举类型。示例如下：
osdba=# SELECT min（weekday）， max（weekday） FROM duty；
osdba=# SELECT * FROM duty where weekday = （SELECT max（weekday） FROM duty）；
每个枚举类型都是独立的，不能与其他枚举类型混用。
一个枚举值在磁盘上占4字节空间。一个枚举值的文本标签长度由NAMEDATALEN 设置并编译到PostgreSQL中，且是以标准编译的方式进行的，也就意味着，一个枚举值的文本标签长度至少是63字节。
枚举类型的值对大小写是敏感的，如“Mon”不等于“mon”。标签中的空格也是一样， 如“Mon”（“Mon”后有一个空格）不等于“Mon”。
枚举类型的函数
上面例子中的枚举类型“week”的定义如下：
CREATE TYPE week AS ENUM （Sun‘， Mon‘， Tues‘， Wed‘， Thur‘， Fri，‘Sat）；
除了两个参数形式的enum_range外，其余函数会忽略传递给它们的具体值，因为它们只关心声明的数据类型。使用null加上类型转换也会得到相同的结果，示例如下：
osdba=# select enum_first（null：week）， enum_last（null：week）；
几何类型
PostgreSQL数据库提供了点、线、矩形、多边形等几何类型，其他数据库大都没有这些类型。
几何类型概况
PostgreSQL主要支持一些二维的几何数据类型。最基本的类型是“point”，它是其也类型的基础。
几何类型的输入
可以使用下面的格式输入几何类型：
类型名称 ‘表现形式’
也可以使用类型转换，形式如下：
‘表现形式’：：类型名称
下面用例子说明如何输入这些几何类型。
点的示例如下：
osdba=# select ‘1,1’::point；
osdba=# select ‘（1,1）’::point;
线段的示例如下:
osdba=# select Iseg ‘1,1,2,2’;
osdba=# select Iseg ‘（1,1）,（2,2）’;
扫一扫
5265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
