PostgreSql详细介绍（三）

最新推荐文章于 2024-05-08 17:10:46 发布

越努力越幸运再努力

最新推荐文章于 2024-05-08 17:10:46 发布

阅读量1.1k

点赞数

分类专栏： PostgreSql

本文链接：https://blog.csdn.net/m0_37253968/article/details/103172840

版权

PostgreSql 专栏收录该内容

8 篇文章 5 订阅

订阅专栏

PostgreSQL 学习手册(常用数据类型)
一、数值类型：

下面是 PostgreSQL 所支持的数值类型的列表和简单说明：

1. 整数类型：

类型 smallint、integer 和 bigint 存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是 integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint。而只有在 integer 的范围不够的时候才使用 bigint，因为前者(integer)绝对快得多。

2. 任意精度数值：

类型 numeric 可以存储最多 1000 位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric 类型上的算术运算比整数类型或者浮点数类型要慢很多。 numeric 字段的最大精度和最大比例都是可以配置的。要声明一个类型为 numeric 的字段，你可以用下面的语法： NUMERIC(precision,scale) 比如数字 23.5141 的精度为 6，而刻度为 4。在目前的 PostgreSQL 版本中，decimal 和 numeric 是等效的。

3. 浮点数类型：

数据类型 real 和 double 是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。

4. Serial(序号)类型： serial 和 bigserial 类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。

CREATE TABLE tablename (

colname SERIAL

);

等价于

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename(

colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL

);

这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个 NOT NULL 约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。还需要另外说明的是，一个 serial 类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。

二、字符类型：

下面是 PostgreSQL 所支持的字符类型的列表和简单说明：

SQL 定义了两种基本的字符类型，varchar(n)和 char(n)，这里的 n 是一个正整数。两种类型都可以存储最多 n 个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char 等于 char(1)，而 varchar 则可以接受任何长度的字串。

MyTest=>

CREATE TABLE testtable(first_col varchar(2));

CREATE TABLE

MyTest=> INSERT INTO testtable VALUES('333');

-插入字符串的长度，超过其字段定义的长度，因此报错。
ERROR: value too long for type character varying(2)

-插入字符串中，超出字段定义长度的部分是空格，因此可以插入，但是空白符被截断。
MyTest=> INSERT INTO testtable VALUES('33 ');

INSERT 0 1

MyTest=> SELECT * FROM testtable;

first_col

-----------

(1 row)

这里需要注意的是，如果是将数值转换成 char(n)或者 varchar(n)，那么超长的数值将被截断成 n 个字符，而不会抛出错误。 MyTest=> select 1234::varchar(2);

varchar

---------

(1 row)

最后需要提示的是，这三种类型之间没有性能差别，只不过是在使用 char 类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在 PostgreSQL 里没有。在大多数情况下，应该使用 text 或者 varchar。
三、日期/时间类型：

下面是 PostgreSQL 所支持的日期/时间类型的列表和简单说明：

1. 日期/时间输入: 任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。

1). 日期:

以下为合法的日期格式列表：

2). 时间：以下为合法的时间格式列表：

3). 时间戳：

时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区。因此，1999-01-08 04:05:06 和 1999-01-08 04:05:06 -8:00 都是有效的数值。

2. 示例：

1). 在插入数据之前先查看 datestyle 系统变量的值：

MyTest=> show datestyle;

DateStyle

-----------

ISO, YMD

(1 row)

2). 创建包含日期、时间和时间戳类型的示例表：

MyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);

CREATE TABLE

3). 插入数据：

MyTest=>

INSERT INTO testtable(id,date_col) VALUES(1, DATE'01/02/03'); --datestyle 为 YMD www.linuxidc.com
INSERT 0 1

MyTest=> SELECT id, date_col FROM testtable;

id | date_col

----+------------

1 | 2001-02-03

(1 row)

MyTest=>

set datestyle = MDY;

SET

MyTest=>

INSERT INTO testtable(id,date_col) VALUES(2, DATE'01/02/03'); --datestyle为 MDY
INSERT 0 1

MyTest=>

SELECT id,date_col FROM testtable;

id | date_col

----+------------

1 | 2001-02-03

2 | 2003-01-02

MyTest=>

INSERT INTO testtable(id,time_col) VALUES(3, TIME'10:20:00'); -插入时间。
INSERT 0 1

MyTest=>

SELECT id,time_col FROM testtable WHERE time_col IS NOT NULL;

id | time_col

----+----------

3 | 10:20:00

(1 row)

MyTest=>

INSERT INTO testtable(id,timestamp_col) VALUES(4, DATE'01/02/03');

INSERT 0 1

MyTest=>

INSERT INTO testtable(id,timestamp_col) VALUES(5, TIMESTAMP'01/02/03 10:20:00');

INSERT 0 1

MyTest=>

SELECT id,timestamp_col FROM testtable WHERE timestamp_col IS NOT NULL;

id | timestamp_col

----+---------------------

4 | 2003-01-02 00:00:00

5 | 2003-01-02 10:20:00

(2 rows)

四、布尔类型：

PostgreSQL 支持标准的 SQL boolean 数据类型。boolean 只能有两个状态之一：真(True)或假(False)。该类型占用 1 个字节。 "真"值的有效文本值是：

TRUE

't'

'true'

'y'

'yes'

'1'

而对于"假"而言，你可以使用下面这些：
FALSE

'f'

'false'

'n'

'no'

'0'

见如下使用方式：

MyTest=>

CREATE TABLE testtable (a boolean, b text);

CREATE TABLE

MyTest=>

INSERT INTO testtable VALUES(TRUE, 'sic est');

INSERT 0 1

MyTest=>

INSERT INTO testtable VALUES(FALSE, 'non est');

INSERT 0 1

MyTest=>

SELECT * FROM testtable;

a | b

---+---------

t | sic est

f | non est

(2 rows)

MyTest=>

SELECT * FROM testtable WHERE a;

a | b

---+---------

t | sic est

(1 row)

MyTest=>

SELECT * FROM testtable WHERE a = true;

a | b

---+---------

t | sic est

(1 row)
五、位串类型：

位串就是一串 1 和 0 的字串。它们可以用于存储和视觉化位掩码。我们有两种类型的 SQL 位类型：bit(n)和 bit varying(n); 这里的 n 是一个正整数。bit 类型的数据必须准确匹配长度 n; 试图存储短些或者长一些的数据都是错误的。类型 bit varying 数据是最长 n 的变长类型；更长的串会被拒绝。写一个没有长度的 bit 等效于 bit(1)，没有长度的 bit varying 相当于没有长度限制。针对该类型，最后需要提醒的是，如果我们明确地把一个位串值转换成 bit(n)，那么它的右边将被截断或者在右边补齐零，直到刚好 n 位，而不会抛出任何错误。类似地，如果我们明确地把一个位串数值转换成 bit varying(n)，如果它超过 n 位，那么它的右边将被截断。见如下具体使用方式：

MyTest=>

CREATE TABLE testtable (a bit(3), b bit varying(5));

CREATE TABLE

MyTest=>

INSERT INTO testtable VALUES (B'101', B'00');

INSERT 0 1

MyTest=> INSERT INTO testtable VALUES (B'10', B'101');

ERROR: bit string length 2 does not match type bit(3)

MyTest=> INSERT INTO testtable VALUES (B'10'::bit(3), B'101');

INSERT 0 1 MyTest=> SELECT * FROM testtable;

a | b
-----+-----

101 | 00

100 | 101

(2 rows)

MyTest=>

SELECT B'11'::bit(3);

bit

-----

110

(1 row)

六、数组：

1. 数组类型声明：

1). 创建字段含有数组类型的表。

CREATE TABLE sal_emp (

name

text,

pay_by_quarter integer[] -还可以定义为 integer[4] 或 integer ARRAY[4]

);

2). 插入数组数据：

MyTest=# INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}');

INSERT 0 1

MyTest=# INSERT INTO sal_emp VALUES ('Carol', ARRAY[21000, 22000, 23000, 24000]);

INSERT 0 1

MyTest=# SELECT * FROM sal_emp;

name | pay_by_quarter

--------+---------------------------

Bill | {11000,12000,13000,14000}

Carol | {21000,22000,23000,24000}

(2 rows)

2. 访问数组：

和其他语言一样，

PostgreSQL 中数组也是通过下标数字(写在方括弧内)的方式进行访问，只是 PostgreSQL 中数组元素的下标是从 1 开始 n 结束。

MyTest=# SELECT pay_by_quarter[3] FROM sal_emp;

pay_by_quarter

----------------

13000

23000

(2 rows)

MyTest=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

name

------

Bill

Carol

(2 rows)

PostgreSQL 中还提供了访问数组范围的功能，即 ARRAY[脚标下界:脚标上界]。

MyTest=# SELECT name,pay_by_quarter[1:3] FROM sal_emp;

name | pay_by_quarter
--------+---------------------

Bill | {11000,12000,13000}

Carol | {21000,22000,23000}

(2 rows)

3. 修改数组：

1). 代替全部数组值：

--UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; 也可以。
MyTest=# UPDATE sal_emp SET pay_by_quarter = '{31000,32000,33000,34000}' WHERE name = 'Carol';

UPDATE 1

MyTest=# SELECT * FROM sal_emp;

name | pay_by_quarter

--------+---------------------------

Bill | {11000,12000,13000,14000}

Carol | {31000,32000,33000,34000}

(2 rows)

2). 更新数组中某一元素：

MyTest=# UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';

UPDATE 1

MyTest=# SELECT * FROM sal_emp;

name | pay_by_quarter

--------+---------------------------

Carol | {31000,32000,33000,34000}

Bill | {11000,12000,13000,15000}

(2 rows)

3). 更新数组某一范围的元素：

MyTest=# UPDATE sal_emp SET pay_by_quarter[1:2] = '{37000,37000}' WHERE name = 'Carol';

UPDATE 1

MyTest=# SELECT * FROM sal_emp;

name | pay_by_quarter

--------+---------------------------

Bill | {11000,12000,13000,15000}

Carol | {37000,37000,33000,34000}

(2 rows)

4). 直接赋值扩大数组：

MyTest=# UPDATE sal_emp SET pay_by_quarter[5] = 45000 WHERE name = 'Bill';

UPDATE 1

MyTest=# SELECT * FROM sal_emp;

name | pay_by_quarter

--------+---------------------------------

Carol | {37000,37000,33000,34000}

Bill | {11000,12000,13000,15000,45000}

(2 rows)

4. 在数组中检索：

1). 最简单直接的方法：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR

pay_by_quarter[2] = 10000 OR

pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;

2). 更加有效的方法：

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); -数组元素中有任何一个等于 10000 ， where
条件将成立。
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); -只有当数组中所有的元素都等于 10000时， where
条件才成立。

七、复合类型：

PostgreSQL 中复合类型有些类似于 C 语言中的结构体，也可以被视为 Oracle 中的记录类型，但是还是感觉复合类型这个命名比较贴切。它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。比如，表字段可以声明为一个复合类型。

1. 声明复合类型:

下面是两个简单的声明示例：

CREATE TYPE complex AS (

r double,

i double

);

CREATE TYPE inventory_item AS (

name text,

supplier_id integer,

price numeric

);

和声明一个数据表相比，声明类型时需要加 AS 关键字，同时在声明 TYPE 时不能定义任何约束。下面我们看一下如何在表中指定复合类型的字段，如：

CREATE TABLE on_hand (

item inventory_item,

count integer

);

最后需要指出的是，在创建表的时候，PostgreSQL 也会自动创建一个与该表对应的复合类型，名字与表字相同，即表示该表的复合类型。

2. 复合类型值输入：

我们可以使用文本常量的方式表示复合类型值，即在圆括号里包围字段值并且用逗号分隔它们。你也可以将任何字段值用双引号括起，如果值本身包含逗号或者圆括号，那么就用双引号括起，对于上面的 inventory_item 复合类型的输入如下：

'("fuzzy dice",42,1.99)'

如果希望类型中的某个字段为 NULL，只需在其对应的位置不予输入即可，如下面的输入中 price 字段的值为 NULL， '("fuzzy dice",42,)'

如果只是需要一个空字串，而非 NULL，写一对双引号，如：

'("",42,)'

在更多的场合中 PostgreSQL 推荐使用 ROW 表达式来构建复合类型值，使用该种方式相对简单，无需考虑更多标识字符问题，如：

ROW('fuzzy dice', 42, 1.99) ROW('', 42, NULL)

注：对于 ROW 表达式，如果里面的字段数量超过 1 个，那么关键字 ROW 就可以省略，因此以上形式可以简化为：

('fuzzy dice', 42, 1.99) ('', 42, NULL)
3. 访问复合类型：

访问复合类型中的字段和访问数据表中的字段在形式上极为相似，只是为了对二者加以区分，PostgreSQL 设定在访问复合类型中的字段时，类型部分需要用圆括号括起，以避免混淆，如：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

如果在查询中也需要用到表名，那么表名和类型名都需要被圆括号括起，如：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

4. 修改复合类型：

见如下几个示例： -直接插入复合类型的数据，这里是通过 ROW 表达式来完成的。
INSERT INTO on_hand(item) VALUES(ROW("fuzzy dice",42,1.99)); -在更新操作中，也是可以通过 ROW 表达式来完成。
UPDATE on_hand SET item = ROW("fuzzy dice",42,1.99) WHERE count = 0; -在更新复合类型中的一个字段时，我们不能在 SET 后面出现的字段名周围加圆括号，
-但是在等号右边的表达式里引用同一个字段时却需要圆括号。
UPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0; -可以在插入中，直接插入复合类型中字段。
INSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);

PostgreSQL 学习手册(函数和操作符<一>)
一、逻辑操作符：

常用的逻辑操作符有：AND、OR 和 NOT。其语义与其它编程语言中的逻辑操作符完全相同。

二、比较操作符：

下面是 PostgreSQL 中提供的比较操作符列表：

比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，且返回 boolean 类型。除了比较操作符以外，我们还可以使用 BETWEEN 语句，如： a BETWEEN x AND y 等效于 a >= x AND a <= y a NOT BETWEEN x AND y 等效于 a < x OR a > y
三、数学函数和操作符：

下面是 PostgreSQL 中提供的数学操作符列表：

按位操作符只能用于整数类型，而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型 bit 和 bit varying，
下面是 PostgreSQL 中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。

三角函数列表：

四、字符串函数和操作符：

下面是 PostgreSQL 中提供的字符串操作符列表：

五、位串函数和操作符：

对于类型 bit 和 bit varying，除了常用的比较操作符之外，还可以使用以下列表中由 PostgreSQL 提供的位串函数和操作符，其中&、|和#的位串操作数必须等长。在移位的时候，保留原始的位串的的长度。

除了以上列表中提及的操作符之外，位串还可以使用字符串函数：length， bit_length， octet_length， position， substring。此外，我们还可以在整数和 bit 之间来回转换，如：

MyTest=# SELECT 44::bit(10);

bit

------------

0000101100

(1 row)

MyTest=# SELECT 44::bit(3);

bit

-----

100

(1 row)

MyTest=# SELECT cast(-44 as bit(12));

bit

--------------

111111010100

(1 row)

MyTest=# SELECT '1110'::bit(4)::integer;

int4
------

(1 row)

注意：如果只是转换为"bit"，意思是转换成 bit(1)，因此只会转换成整数的最低位。
PostgreSQL 学习手册(函数和操作符<二>)
六、模式匹配：

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 ''的方式有效地关闭转义机制，此时，我们就不能关闭下划线和百分号的特殊含义了。关键字 ILIKE 可以用于替换 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)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

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

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

八、时间/日期函数和操作符：

1. 下面是 PostgreSQL 中支持的时间/日期操作符的列表：

2. 日期/时间函数：

3. EXTRACT，date_part 函数支持的 field：

4. 当前日期/时间：

我们可以使用下面的函数获取当前的日期和/或时间∶

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME (precision)

CURRENT_TIMESTAMP (precision)

LOCALTIME

LOCALTIMESTAMP

LOCALTIME (precision)

LOCALTIMESTAMP (precision)
PostgreSQL 学习手册(函数和操作符<三>)
九、序列操作函数：

序列对象(也叫序列生成器)都是用 CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。
函数
返回类型
描述
nextval(regclass) bigint
递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行 nextval，每个进程也会安全地收到一个唯一的序列值。
currval(regclass) bigint
在当前会话中返回最近一次 nextval 抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval，那么会报告一个错误。 )请注意因为此函数返回一个会话范围的数值，而且也能给出一个可预计的结果，因此可以用于判断其它会话是否执行过 nextval。
lastval() bigint
返回当前会话里最近一次 nextval 返回的数值。这个函数等效于 currval，只是它不用序列名为参数，它抓取当前会话里面最近一次 nextval 使用的序列。如果当前会话还没有调用过 nextval，那么调用 lastval 将会报错。
setval(regclass, bigint)
bigint
重置序列对象的计数器数值。设置序列的 last_value 字段为指定数值并且将其 is_called 字段设置为 true，表示下一次 nextval 将在返回数值之前递增该序列。
setval(regclass, bigint, boolean)
bigint
重置序列对象的计数器数值。功能等同于上面的 setval 函数，只是 is_called 可以设置为 true 或 false。如果将其设置为 false，那么下一次 nextval 将返回该数值，随后的 nextval 才开始递增该序列。 www.linuxidc.com
对于 regclass 参数，仅需用单引号括住序列名即可，因此它看上去就像文本常量。为了达到和处理普通 SQL 对象一样的兼容性，这个字串将被转换成小写，除非该序列名是用双引号括起，如： nextval('foo') -操作序列号 foo nextval('FOO') -操作序列号 foo nextval('"Foo"') -操作序列号 Foo SELECT setval('foo', 42); -下次 nextval 将返回 43 SELECT setval('foo', 42, true); SELECT setval('foo', 42, false); -下次 nextval 将返回 42

十、条件表达式：

1. CASE：

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

CASE WHEN condition THEN result

[WHEN ...]

[ELSE result]

END

condition 是一个返回 boolean 的表达式。如果为真，那么 CASE 表达式的结果就是符合条件的 result。如果结果为假，那么以相同方式搜寻随后的 WHEN 子句。如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。如果省略了 ELSE 子句而且没有匹配的条件，结果为 NULL，如：

MyTest=> SELECT * FROM testtable;

---

(3 rows)

MyTest=> SELECT i, CASE WHEN i=1 THEN 'one'

MyTest-> WHEN i=2 THEN 'two'

MyTest-> ELSE 'other'

MyTest-> END

MyTest-> FROM testtable;

i | case

---+-------

1 | one

2 | two

3 | other

(3 rows)

注：CASE 表达式并不计算任何对于判断结果并不需要的子表达式。

2. COALESCE：

COALESCE 返回它的第一个非 NULL 的参数的值。它常用于在为显示目的检索数据时用缺省值替换 NULL 值。 COALESCE(value[, ...]) 和 CASE 表达式一样，COALESCE 将不会计算不需要用来判断结果的参数。也就是说，在第一个非空参数右边的参数不会被计算。

3. NULLIF：当且仅当 value1 和 value2 相等时，NULLIF 才返回 NULL。否则它返回 value1。

NULLIF(value1, value2)
MyTest=> SELECT NULLIF('abc','abc');

nullif

--------

(1 row)

MyTest=> SELECT NULLIF('abcd','abc');

nullif

--------

abcd

(1 row)

4. GREATEST 和 LEAST：

GREATEST 和 LEAST 函数从一个任意的数字表达式列表里选取最大或者最小的数值。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。

GREATEST(value [, ...]) LEAST(value [, ...])

MyTest=> SELECT GREATEST(1,3,5);

greatest

----------

(1 row)

MyTest=> SELECT LEAST(1,3,5,NULL);

least

-------

(1 row)
十一、数组函数和操作符：

1. PostgreSQL 中提供的用于数组的操作符列表：

2. PostgreSQL 中提供的用于数组的函数列表：

十二、系统信息函数：

1. PostgreSQL 中提供的和数据库相关的函数列表：

2. 允许用户在程序里查询对象访问权限的函数：

注：以上函数均返回 boolean类型。要评估一个用户是否在权限上持有赋权选项，给权限键字附加 WITH GRANT OPTION ；比
如 'UPDATE WITH GRANT OPTION' 。

3. 模式可视性查询函数：

那些判断一个对象是否在当前模式搜索路径中可见的函数。如果一个表所在的模式在搜索路径中，并且没有同名的表出现在搜索路径的更早的地方，那么就说这个表视可见的。它等效于表可以不带明确模式修饰进行引用。

注：以上函数均返回 boolean类型，所有这些函数都需要对象 OID
标识作为检查的对象。
postgres=# SELECT pg_table_is_visible('testtable'::regclass);

pg_table_is_visible

---------------------

(1 row)

4. 系统表信息函数：

这些函数大多数都有两个变种，其中一个可以选择对结果的"漂亮的打印"。漂亮打印的格式更容易读，但是缺省的格式更有可能被将来的 PostgreSQL 版本用同样的方法解释；如果是用于转储，那么尽可能避免使用漂亮打印。给漂亮打印参数传递 false 生成的结果和那个没有这个参数的变种生成的结果完全一样。

十三、系统管理函数：

1. 查询以及修改运行时配置参数的函数：

current_setting 用于以查询形式获取 setting_name 设置的当前数值。它和 SQL 命令 SHOW 是等效的。比如：

MyTest=# SELECT current_setting('datestyle');

current_setting

-----------------

ISO, YMD

(1 row)

set_config 将参数 setting_name 设置为 new_value。如果 is_local 设置为 true，那么新数值将只应用于当前事务。如果你希望新的数值应用于当前会话，那么应该使用 false。它等效于 SQL 命令 SET。比如：

MyTest=# SELECT set_config('log_statement_stats','off', false);

set_config

------------
off

(1 row)

2. 数据库对象尺寸函数：

3. 数据库对象位置函数：

mydatabase=# select pg_relation_filenode('testtable');

pg_relation_filenode

----------------------

17877

(1 row)

mydatabase=# select pg_relation_filepath('testtable');

pg_relation_filepath

----------------------------------------------

pg_tblspc/17633/PG_9.1_201105231/17636/17877

(1 row)

越努力越幸运再努力

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
PostgreSql详细介绍（三）

PostgreSQL 学习手册(常用数据类型)一、数值类型：下面是 PostgreSQL 所支持的数值类型的列表和简单说明： 1. 整数类型：类型 smallint、integer 和 bigint 存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是 integer，因为它提供了在范围、存储空间和...
复制链接

扫一扫