4、PostgreSQL数据范例

PostgreSQL数据范例

PostgreSQL的数据范例是许多的，而且用户可以利用命令CREATE TYPE来添加新的数据范例。所以我就挑了一些来做笔记，想要了解更多可以看官方文档。
数据范例表：
名字别名描述bigintint8有符号的8字节整数bigserialserial8主动增长的8字节整数bit [ (*n*) ]定长位串bit varying [ (*n*) ]varbit变长位串booleanbool逻辑布尔值（真/假）box平面上的普通方框bytea二进制数据（“字节数组”）character [ (*n*) ]char [ (*n*) ]定长字符串character varying [ (*n*) ]varchar [ (*n*) ]变长字符串cidrIPv4或IPv6网络地址circle平面上的圆date日历日期（年、月、日）double precisionfloat8双精度浮点数（8字节）inetIPv4或IPv6主机地址integerint, int4有符号4字节整数interval [ *fields* ] [ (*p*) ]时间段json文本 JSON 数据jsonb二进制 JSON 数据，已分解line平面上的无穷长的线lseg平面上的线段macaddrMAC（Media Access Control）地址macaddr8MAC (Media Access Control) 地址 (EUI-64 格式)money钱币数量numeric [ (*p*, *s*) ]decimal [ (*p*, *s*) ]可选择精度的准确数字path平面上的几何路径pg_lsnPostgreSQL日志序列号point平面上的几何点polygon平面上的封闭几何路径realfloat4单精度浮点数（4字节）smallintint2有符号2字节整数smallserialserial2主动增长的2字节整数serialserial4主动增长的4字节整数text变长字符串time [ (*p*) ] [ without time zone ]一天中的时间（无时区）time [ (*p*) ] with time zonetimetz一天中的时间，包括时区timestamp [ (*p*) ] [ without time zone ]日期和时间（无时区）timestamp [ (*p*) ] with time zonetimestamptz日期和时间，包括时区tsquery文本搜刮查询tsvector文本搜刮文档txid_snapshot用户级别事件ID快照uuid通用唯一标识码xmlXML数据 1、数字范例

数字范例由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数构成。
名字存储尺寸描述范围smallint2字节小范围整数-32768 to +32767integer4字节整数的典型选择-2147483648 to +2147483647bigint8字节大范围整数-9223372036854775808 to +9223372036854775807decimal可变用户指定精度，准确最高小数点前131072位，以及小数点后16383位numeric可变用户指定精度，准确最高小数点前131072位，以及小数点后16383位real4字节可变精度，不准确6位十进制精度double precision8字节可变精度，不准确15位十进制精度smallserial2字节主动增加的小整数1到32767serial4字节主动增加的整数1到2147483647bigserial8字节主动增长的大整数1到9223372036854775807 1.1、整数范例

范例smallint、integer和bigint存储各种范围的整数。
常用的范例是integer，由于它提供了在范围、存储空间和性能之间的最佳平衡。一样平常只有在磁盘空间紧张的时间才利用 smallint范例。而只有在integer的范围不够的时间才利用bigint。
1.2、恣意精度数字

范例numeric可以存储非常多位的数字。不外，numeric范例上的算术运算比整数范例大概浮点数范例要慢许多。
**一个numeric的比例是到小数部分的位数，numeric的精度是整个数字里全部位的数目，也就是小数点两边的位数目。因此数字 23.5141 的精度为6而比例为4。**你可以以为整数的比例为零。
numeric范例的声明：

1. NUMERIC(precision, scale)

复制代码

精度必须为正数，比例可以为零大概正数。
除了普通的数字值之外，numeric范例答应特别值NaN， 表现“不是一个数字”。任安在 NaN上面的利用都生成另外一个NaN。 假如在 SQL 命令里把这些值当作一个常量写，你必须在其四周放上单引号，比方UPDATE table SET x = 'NaN'。
1.3、浮点范例

数据范例real和double precision是不精确的、变精度的数字范例，也就是浮点数范例。
浮点范例还有几个特别值：

• Infinity：正无穷大
  
• -Infinity：负无穷小
  
• NaN：不是一个数字
  

PostgreSQL还支持 SQL 尺度表现法float和float(*p*)用于声明非准确的数字范例。p指定以*二进制***位表现的最低可接受精度
1.4、序数范例

smallserial、serial和bigserial范例不是真正的范例，它们只是为了创建唯一标识符列而存在的方便符号（雷同别的一些数据库中支持的AUTO_INCREMENT属性）。
2、钱币范例

money范例存储固定小数精度的钱币数字。
名字存储尺寸描述范围money8 bytes钱币额-92233720368547758.08到+92233720368547758.07 3、字符范例

名字描述character varying(*n*), varchar(*n*)有限定的变长character(*n*), char(*n*)定长，空格填充text无穷变长 SQL界说了两种基本的字符范例： character varying(*n*)和character(*n*)， 其中*n是一个正整数。两种范例都可以存储最多n*个字符长的串。试图存储更长的串到这些范例的列里会产生一个错误， 除非超出长度的字符都是空缺，这种情况下该串将被截断为最大长度。 假如要存储的串比声明的长度短，范例为character的值将会用空缺填满；而范例为character varying的值将只是存储短些的串。
提示

这三种范例之间没有性能差别，只不外是在利用填充空缺的范例的时间需要更多存储尺寸，以及在存储到一个有长度束缚的列时需要少量额外CPU周期来检查长度。虽然在某些别的的数据库体系里，character(*n*)有一定的性能优势，但在PostgreSQL里没有。究竟上，character(*n*)通常是这三种范例之中最慢的一个，由于它需要额外的存储开销。在大多数情况下，应该利用text大概character varying。
字符范例的利用

1. CREATE TABLE test1 (a character(4));
2. INSERT INTO test1 VALUES ('ok');
3. SELECT a, char_length(a) FROM test1; -- (1)
4.   a   | char_length
5. ------+-------------
6. ok   |           2
8. CREATE TABLE test2 (b varchar(5));
9. INSERT INTO test2 VALUES ('ok');
10. INSERT INTO test2 VALUES ('good      ');
11. INSERT INTO test2 VALUES ('too long');
12. ERROR:  value too long for type character varying(5)
13. INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
14. SELECT b, char_length(b) FROM test2;
15.    b   | char_length
16. -------+-------------
17. ok    |           2
18. good  |           5
19. too l |           5

复制代码

4、二进制数范例

bytea数据范例答应存储二进制串
名字存储尺寸描述bytea1或4字节外加真正的二进制串变长二进制串 简单说，二进制字串实用于存储那些步伐员以为是“裸字节”的数据，而字符串适合存储文本。
SQL尺度界说了一种差别的二进制串范例， 叫做BLOB大概BINARY LARGE OBJECT。其输入格式和bytea差别，但是提供的函数和利用符大多一样。
5、日期/时间范例

PostgreSQL支持SQL中所有的日期和时间范例，如下表所示。
名字存储尺寸描述最小值最大值解析度timestamp [ (*p*) ] [ without time zone ]8字节包括日期和时间（无时区）4713 BC294276 AD1微秒 / 14位timestamp [ (*p*) ] with time zone8字节包括日期和时间，有时区4713 BC294276 AD1微秒 / 14位date4字节日期（没有一天中的时间）4713 BC5874897 AD1日time [ (*p*) ] [ without time zone ]8字节一天中的时间（无日期）00:00:0024:00:001微秒 / 14位time [ (*p*) ] with time zone12字节一天中的时间（不带日期），带有时区00:00:00+145924:00:00-14591微秒 / 14位interval [ *fields* ] [ (*p*) ]16字节时间间隔-178000000年178000000年1微秒 / 14位 time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界，*p*答应的范围是从 0 到 6。
interval范例有一个附加选项，它可以通过写下面之一的短语来限定存储的fields的集合：

1. YEAR
2. MONTH
3. DAY
4. HOUR
5. MINUTE
6. SECOND
7. YEAR TO MONTH
8. DAY TO HOUR
9. DAY TO MINUTE
10. DAY TO SECOND
11. HOUR TO MINUTE
12. HOUR TO SECOND
13. MINUTE TO SECOND

复制代码

注意假如*fields和p被指定，fields*必须包括SECOND，由于精度只应用于秒。
5.1、日期/时间输入

日期和时间的输入可以接受几乎任何合理的格式，把[DateStyle参数设置为MDY，就是选择“月－日－年”的表明，设置为DMY就是 “日－月－年”，而YMD是 “年－月－日”。
SQL语法：

1. type [ (p) ] 'value'

复制代码

其中*p*是一个可选的精度声明，它给出了在秒域中的小数位数目。精度可以被指定给time、timestamp和interval范例，范围是0到6。假如在一个常数声明中没有指定任何精度，它将默认取文字值的精度（但是不能凌驾6位）。
5.1.1、日期

date范例可能的输入方式：
例子描述1999-01-08ISO 8601; 任何模式下的1月8日 （推荐格式）January 8, 1999在任何datestyle输入模式下都无歧义1/8/1999MDY模式中的1月8日；DMY模式中的8月1日1/18/1999MDY模式中的1月18日；在其他模式中被拒绝01/02/03MDY模式中的2003年1月2日； DMY模式中的2003年2月1日； YMD模式中的2001年2月3日1999-Jan-08任何模式下的1月8日Jan-08-1999任何模式下的1月8日08-Jan-1999任何模式下的1月8日99-Jan-08YMD模式中的1月8日，否则错误08-Jan-991月8日，除了在YMD模式中错误Jan-08-991月8日，除了在YMD模式中错误19990108ISO 8601; 任何模式中的1999年1月8日990108ISO 8601; 任何模式中的1999年1月8日1999.008年和一年中的日子J2451187儒略日期January 8, 99 BC公元前99年 5.1.2、时间

当日时间范例是time [ (*p*) ] without time zone和time [ (*p*) ] with time zone。 只写time等效于time without time zone。
time范例可能的输入方式：
例子描述04:05:06.789ISO 860104:05:06ISO 860104:05ISO 8601040506ISO 860104:05 AM和04:05一样，AM并不影响值04:05 PM和16:05一样，输入的小时必须为 <= 1204:05:06.789-8ISO 860104:05:06-08:00ISO 860104:05-08:00ISO 8601040506-08ISO 860104:05:06 PST缩写指定的时区2003-04-12 04:05:06 America/New_York全名指定的时区 5.1.3、时间戳

时间戳范例的有效输入由一个日期和时间的串接构成，背面跟着一个可选的时区，一个可选的AD大概BC
下面是一些时间戳的写法：

1. 1999-01-08 04:05:06

复制代码

1. 1999-01-08 04:05:06
2. -8:00

复制代码

1. January 8 04:05:06 1999 PST

复制代码

5.1.4、特别值

输入串合法范例描述epochdate, timestamp1970-01-01 00:00:00+00（Unix体系时间0）infinitydate, timestamp比任何其他时间戳都晚-infinitydate, timestamp比任何其他时间戳都早nowdate, time, timestamp当前事件的开始时间todaydate, timestamp当日午夜tomorrowdate, timestamp明日午夜yesterdaydate, timestamp昨日午夜allballstime00:00:00.00 UTC 了解即可
5.2、日期/时间输出

时间/日期范例的输特别式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。
日期/时间输出风格
风格声明描述例子ISOISO 8601, SQL尺度1997-12-17 07:37:16-08SQL传统风格12/17/1997 07:37:16.00 PSTPostgres原始风格Wed Dec 17 07:37:16 1997 PSTGerman地区风格17.12.1997 07:37:16.00 PST 日期序次风俗
datestyle设置输入序次例子输出SQL, DMY日/月/年17/12/1997 15:37:16.00 CETSQL, MDY月/日/年12/17/1997 07:37:16.00 PSTPostgres, DMY日/月/年Wed 17 Dec 07:37:16 1997 PST 6、布尔范例

PostgreSQL提供尺度的SQL范例boolean，boolean可以有多个状态：“true（真）”、“false（假）”和第三种状态“unknown（未知）”，未知状态由SQL空值表现。
名字存储字节描述boolean1字节状态为真大概假 “真”状态的有效文字值是：
TRUE't''true''y''yes''on''1' 而对于“假”状态，你可以利用下面这些值：
FALSE'f''false''n''no''off''0' 推荐利用TRUE和FALSE
boolean范例的利用

1. CREATE TABLE test1 (a boolean, b text);
2. INSERT INTO test1 VALUES (TRUE, 'sic est');
3. INSERT INTO test1 VALUES (FALSE, 'non est');
4. SELECT * FROM test1;
5. a |    b
6. ---+---------
7. t | sic est
8. f | non est
10. SELECT * FROM test1 WHERE a;
11. a |    b
12. ---+---------
13. t | sic est

复制代码

7、JSON范例

JSON 数据范例是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据范例的 优势在于能逼迫要求每个被存储的值符合 JSON 规则。也有许多 JSON 相关的函 数和利用符可以用于存储在这些数据范例中的数据。
jSON的俩种数据范例：json和jsonb

它们几乎相同，唯一的实际区别之一就是效率。
json数据范例存储输入文本的精准拷贝，处置惩罚函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，由于需要做附加的转换。但是 jsonb在处置惩罚时要快许多，由于不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。
JSON 基本范例和相应的PostgreSQL范例

JSON 基本范例PostgreSQL范例注释stringtext不答应\u0000，假如数据库编码不是 UTF8，非 ASCII Unicode 转义也是如许numbernumeric不答应NaN 和 infinity值booleanboolean只接受小写true和false拼写null(无)SQL NULL是一个差别的概念 回头在写
8、数组

PostgreSQL答应一个表中的列界说为变长多维数组。可以创建任何内建或用户界说的基类、枚举范例或组合范例的数组。
8.1、数组范例的界说

为了展示数组范例的利用，我们创建如许一个表：

1. CREATE TABLE sal_emp (
2.     name            text,
3.     pay_by_quarter  integer[],
4.     schedule        text[][]
5. );

复制代码

一个数组数据范例可以通过在数组元素的数据范例名称背面加上方括号（[]）来定名。
CREATE TABLE的语法答应指定数组简直切大小，比方：

1. CREATE TABLE tictactoe (
2.     squares   integer[3][3]
3. );

复制代码

8.2、数组值输入

一个数组常量的一样平常格式如下：

1. '{ val1 delim val2 delim ... }'

复制代码

这里*delim*是范例的定界符，比如说逗号、分号（用于box范例）。
数组常量的例子：

1. '{{1,2,3},{4,5,6},{7,8,9}}'

复制代码

要设置一个数组常量的一个元素为NULL，在该元素值处写NULL。假如你需要一个真正的字符串值“NULL”，你必须在它两边放上双引号。
8.3、详细利用

1. INSERT INTO sal_emp
2.     VALUES ('Bill',
3.     '{10000, 10000, 10000, 10000}',
4.     '{{"meeting", "lunch"}, {"training", "presentation"}}');
6. INSERT INTO sal_emp
7.     VALUES ('Carol',
8.     '{20000, 25000, 25000, 25000}',
9.     '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

复制代码

看看结果

1. SELECT * FROM sal_emp;
2. name  |      pay_by_quarter       |                 schedule
3. -------+---------------------------+-------------------------------------------
4. Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
5. Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
6. (2 rows)

复制代码

多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，比方：

1. INSERT INTO sal_emp
2.     VALUES ('Bill',
3.     '{10000, 10000, 10000, 10000}',
4.     '{{"meeting", "lunch"}, {"meeting"}}');
5. ERROR:  multidimensional arrays must have array expressions with matching dimensions

复制代码

8.4、访问数组

查询检索在第二季度工资发生变化的雇员的名字：

1. SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
3. name
4. -------
5. Carol
6. (1 row)

复制代码

数组下标写在方括号内。默认情况下，PostgreSQL为数组利用了一种从1开始的编号风俗，即一个具有*n*个元素的数组从array[1]开始，结束于array[*n*]。
查询检索所有员工第三季度的工资：

1. SELECT pay_by_quarter[3] FROM sal_emp;
3. pay_by_quarter
4. ----------------
5.           10000
6.           25000
7. (2 rows)

复制代码

这个数组也可以切片哦！
一个数组切片可以通过在一个或多个数组维度上指定*下界*:*上界*来界说
查询检索Bill在本周头两天日程中的第一项：

1. SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
3.         schedule
4. ------------------------
5. {{meeting},{training}}
6. (1 row)

复制代码

利用array_dims函数得到任何数组值的当前维度：

1. SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
3. array_dims
4. ------------
5. [1:2][1:2]
6. (1 row)

复制代码

array_dims产生一个text结果
也可以通过array_upper和array_lower来得到维度，它们将分别返回一个指定数组的上界和下界：

1. SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
3. array_upper
4. -------------
5.            2
6. (1 row)

复制代码

array_length将返回一个指定数组维度的长度：

1. SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
3. array_length
4. --------------
5.             2
6. (1 row)

复制代码

cardinality返回一个数组中在所有维度上的元素总数。 这实际上是调用unnest将会得到的行数：

1. SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
3. cardinality
4. -------------
5.            4
6. (1 row)

复制代码

8.5、修改数组

数组值可以被整个替换：

1. UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
2.     WHERE name = 'Carol';

复制代码

大概利用ARRAY表达式语法：

1. UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
2.     WHERE name = 'Carol';

复制代码

更新数组上的一个元素：

1. UPDATE sal_emp SET pay_by_quarter[4] = 15000
2.     WHERE name = 'Bill';

复制代码

大概在一个切片上被更新：

1. UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
2.     WHERE name = 'Carol';

复制代码

一个数组也可以通过利用函数array_prepend、array_append或array_cat构建。前两个函数仅支持一维数组，但array_cat支持多维数组。 一些例子：

1. SELECT array_prepend(1, ARRAY[2,3]);
2. array_prepend
3. ---------------
4. {1,2,3}
5. (1 row)
7. SELECT array_append(ARRAY[1,2], 3);
8. array_append
9. --------------
10. {1,2,3}
11. (1 row)
13. SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
14. array_cat
15. -----------
16. {1,2,3,4}
17. (1 row)
19. SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
20.       array_cat
21. ---------------------
22. {{1,2},{3,4},{5,6}}
23. (1 row)
25. SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
26.       array_cat
27. ---------------------
28. {{5,6},{1,2},{3,4}}

复制代码

8.6、在数组中搜刮

要在一个数组中搜刮一个值，每一个值都必须被检查。这可以手动完成，但是我们必须知道数组的尺寸。比方：

1. SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
2.                             pay_by_quarter[2] = 10000 OR
3.                             pay_by_quarter[3] = 10000 OR
4.                             pay_by_quarter[4] = 10000;

复制代码

但是这么写好像有点呆呆的，而且数组长度未知的时间无法利用。那么就可以用到ANY和ALL了。

1. SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

复制代码

查找所有元素值都为10000的数组所在的行：

1. SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

复制代码

太多了，记笔记记不完。。。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。