PostgreSQL 基本数据类型及常用SQL 函数操作

数据类型

名字

别名

描述

bigint

int8

有符号的8字节整数

bigserial

serial8

自动增长的8字节整数

bit [ (n) ]

定长位串

bit varying [ (n) ]

varbit

变长位串

boolean

bool

逻辑布尔值(真/假)

box

平面上的普通方框

bytea

二进制数据(“字节数组”)

character [ (n) ]

char [ (n) ]

定长字符串

character varying [ (n) ]

varchar [ (n) ]

变长字符串

cidr

IPv4或IPv6网络地址

circle

平面上的圆

date

日历日期(年、月、日)

double precision

float8

双精度浮点数(8字节)

inet

IPv4或IPv6主机地址

integer

int, int4

有符号4字节整数

interval [ fields ] [ (p) ]

时间段

json

文本 JSON 数据

jsonb

二进制 JSON 数据,已分解

line

平面上的无限长的线

lseg

平面上的线段

macaddr

MAC(Media Access Control)地址

macaddr8

MAC (Media Access Control) 地址 (EUI-64 格式)

money

货币数量

numeric [ (p, s) ]

decimal [ (p, s) ]

可选择精度的精确数字

path

平面上的几何路径

pg_lsn

PostgreSQL日志序列号

point

平面上的几何点

polygon

平面上的封闭几何路径

real

float4

单精度浮点数(4字节)

smallint

int2

有符号2字节整数

smallserial

serial2

自动增长的2字节整数

serial

serial4

自动增长的4字节整数

text

变长字符串

time [ (p) ] [ without time zone ]

一天中的时间(无时区)

time [ (p) ] with time zone

timetz

一天中的时间,包括时区

timestamp [ (p) ] [ without time zone ]

日期和时间(无时区)

timestamp [ (p) ] with time zone

timestamptz

日期和时间,包括时区

tsquery

文本搜索查询

tsvector

文本搜索文档

txid_snapshot

用户级别事务ID快照

uuid

通用唯一标识码

xml

XML数据

1. 查询服务器当前时间及日期

select now(); 取当前日期及时间

select current_time;

select current_date;

select extract(YEAR from now()); 取当前日期的年

select extract(month from now()); //取当前月

select extract(day from now()); //取当前日

2. 字符串操作

select 'aaaaa'||'bbbbbb' as f1; //字符串相加

select char_length('abcdefgh'); //字符串长度

select position('fgh' in 'abcdefgh'); //查找子串

select substring ( 'abcdefgh' from 5 for 3); //取一段字符串

select lower( 'abCDefgh')||upper('abCDefgh')

3. 日期转字符串

select to_char(now(), 'yyyy-mm-dd hh:mi:ss');

更多的函数和操作见http://www.postgres.cn/docs/11/functions.html

一、逻辑操作符:

常用的逻辑操作符有: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中提供的数学操作符列表:

操作符

描述

例子

结果

+

2 + 3

5

-

2 - 3

-1

*

2 * 3

6

/

4 / 2

2

%

5 % 4

1

^

2.0 ^ 3.0

8

|/

平方根

|/ 25.0

5

||/

立方根

||/ 27.0

3

!

阶乘

5 !

120

!!

阶乘

!! 5

120

@

绝对值

@ -5.0

5

&

按位AND

91 & 15

11

|

按位OR

32 | 3

35

#

按位XOR

17 # 5

20

~

按位NOT

~1

-2

<<

按位左移

1 << 4

16

>>

按位右移

8 >> 2

2

按位操作符只能用于整数类型,而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying,

下面是PostgreSQL中提供的数学函数列表,需要说明的是,这些函数中有许多都存在多种形式,区别只是参数类型不同。除非特别指明,任何特定形式的函数都返回和它的参数相同的数据类型。

函数

返回类型

描述

例子

结果

abs(x)

绝对值

abs(-17.4)

17.4

cbrt(double)

立方根

cbrt(27.0)

3

ceil(double/numeric)

不小于参数的最小的整数

ceil(-42.8)

-42

degrees(double)

把弧度转为角度

degrees(0.5)

28.6478897565412

exp(double/numeric)

自然指数

exp(1.0)

2.71828182845905

floor(double/numeric)

不大于参数的最大整数

floor(-42.8)

-43

ln(double/numeric)

自然对数

ln(2.0)

0.693147180559945

log(double/numeric)

10为底的对数

log(100.0)

2

log(b numeric,x numeric)

numeric指定底数的对数

log(2.0, 64.0)

6.0000000000

mod(y, x)

取余数

mod(9,4)

1

pi()

double

"π"常量

pi()

3.14159265358979

power(a double, b double)

double

求a的b次幂

power(9.0, 3.0)

729

power(a numeric, b numeric)

numeric

求a的b次幂

power(9.0, 3.0)

729

radians(double)

double

把角度转为弧度

radians(45.0)

0.785398163397448

random()

double

0.0到1.0之间的随机数值

random()

round(double/numeric)

圆整为最接近的整数

round(42.4)

42

round(v numeric, s int)

numeric

圆整为s位小数数字

round(42.438,2)

42.44

sign(double/numeric)

参数的符号(-1,0,+1)

sign(-8.4)

-1

sqrt(double/numeric)

平方根

sqrt(2.0)

1.4142135623731

trunc(double/numeric)

截断(向零靠近)

trunc(42.8)

42

trunc(v numeric, s int)

numeric

截断为s小数位置的数字

trunc(42.438,2)

42.43

三角函数列表:

函数

描述

acos(x)

反余弦

asin(x)

反正弦

atan(x)

反正切

atan2(x, y)

正切 y/x 的反函数

cos(x)

余弦

cot(x)

余切

sin(x)

正弦

tan(x)

正切

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

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

函数

返回类型

描述

例子

结果

string || string

text

字串连接

'Post' || 'greSQL'

PostgreSQL

bit_length(string)

int

字串里二进制位的个数

bit_length('jose')

32

char_length(string)

int

字串中的字符个数

char_length('jose')

4

convert(string using conversion_name)

text

使用指定的转换名字改变编码。

convert('PostgreSQL' using iso_8859_1_to_utf8)

'PostgreSQL'

lower(string)

text

把字串转化为小写

lower('TOM')

tom

octet_length(string)

int

字串中的字节数

octet_length('jose')

4

overlay(string placing string from int [for int])

text

替换子字串

overlay('Txxxxas' placing 'hom' from 2 for 4)

Thomas

position(substring in string)

int

指定的子字串的位置

position('om' in 'Thomas')

3

substring(string [from int] [for int])

text

抽取子字串

substring('Thomas' from 2 for 3)

hom

substring(string from pattern)

text

抽取匹配 POSIX 正则表达式的子字串

substring('Thomas' from '...$')

mas

substring(string from pattern for escape)

text

抽取匹配SQL正则表达式的子字串

substring('Thomas' from '%#"o_a#"_' for '#')

oma

trim([leading | trailing | both] [characters] from string)

text

从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串

trim(both 'x' from 'xTomxx')

Tom

upper(string)

text

把字串转化为大写。

upper('tom')

TOM

ascii(text)

int

参数第一个字符的ASCII码

ascii('x')

120

btrim(string text [, characters text])

text

从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串

btrim('xyxtrimyyx','xy')

trim

chr(int)

text

给出ASCII码的字符

chr(65)

A

convert(string text, [src_encoding name,] dest_encoding name)

text

把字串转换为dest_encoding

convert( 'text_in_utf8', 'UTF8', 'LATIN1')

以ISO 8859-1编码表示的text_in_utf8

initcap(text)

text

把每个单词的第一个子母转为大写,其它的保留小写。单词是一系列字母数字组成的字符,用非字母数字分隔。

initcap('hi thomas')

Hi Thomas

length(string text)

int

string中字符的数目

length('jose')

4

lpad(string text, length int [, fill text])

text

通过填充字符fill(缺省时为空白),把string填充为长度length。 如果string已经比length长则将其截断(在右边)。

lpad('hi', 5, 'xy')

xyxhi

ltrim(string text [, characters text])

text

从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。

ltrim('zzzytrim','xyz')

trim

md5(string text)

text

计算给出string的MD5散列,以十六进制返回结果。

md5('abc')

repeat(string text, number int)

text

重复string number次。

repeat('Pg', 4)

PgPgPgPg

replace(string text, from text, to text)

text

把字串string里出现地所有子字串from替换成子字串to。

replace('abcdefabcdef', 'cd', 'XX')

abXXefabXXef

rpad(string text, length int [, fill text])

text

通过填充字符fill(缺省时为空白),把string填充为长度length。如果string已经比length长则将其截断。

rpad('hi', 5, 'xy')

hixyx

rtrim(string text [, character text])

text

从字串string的结尾删除只包含character(缺省是个空白)的最长的字

rtrim('trimxxxx','x')

trim

split_part(string text, delimiter text, field int)

text

根据delimiter分隔string返回生成的第field个子字串(1 Base)。

split_part('abc~@~def~@~ghi', '~@~', 2)

def

strpos(string, substring)

text

声明的子字串的位置。

strpos('high','ig')

2

substr(string, from [, count])

text

抽取子字串。

substr('alphabet', 3, 2)

ph

to_ascii(text [, encoding])

text

把text从其它编码转换为ASCII。

to_ascii('Karel')

Karel

to_hex(number int/bigint)

text

把number转换成其对应地十六进制表现形式。

to_hex(9223372036854775807)

7fffffffffffffff

translate(string text, from text, to text)

text

把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。

translate('12345', '14', 'ax')

a23x5

五、位串函数和操作符:

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

操作符

描述

例子

结果

||

连接

B'10001' || B'011'

10001011

&

按位AND

B'10001' & B'01101'

00001

|

按位OR

B'10001' | B'01101'

11101

#

按位XOR

B'10001' # B'01101'

11100

~

按位NOT

~ B'10001'

01110

<<

按位左移

B'10001' << 3

01000

>>

按位右移

B'10001' >> 2

00100

除了以上列表中提及的操作符之外,位串还可以使用字符串函数: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

------

14

六、模式匹配:

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。还有!~~和!~~*操作符分别代表NOTLIKE和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、floatingpoint和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的调用习惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板。

函数

返回类型

描述

例子

to_char(timestamp, text)

text

把时间戳转换成字串

to_char(current_timestamp, 'HH12:MI:SS')

to_char(interval, text)

text

把时间间隔转为字串

to_char(interval '15h 2m 12s', 'HH24:MI:SS')

to_char(int, text)

text

把整数转换成字串

to_char(125, '999')

to_char(double precision, text)

text

把实数/双精度数转换成字串

to_char(125.8::real, '999D9')

to_char(numeric, text)

text

把numeric转换成字串

to_char(-125.8, '999D99S')

to_date(text, text)

date

把字串转换成日期

to_date('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(text, text)

timestamp

把字串转换成时间戳

to_timestamp('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(double)

timestamp

把UNIX纪元转换成时间戳

to_timestamp(200120400)

to_number(text, text)

numeric

把字串转换成numeric

to_number('12,454.8-', '99G999D9S')

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

模式

描述

HH

一天的小时数(01-12)

HH12

一天的小时数(01-12)

HH24

一天的小时数(00-23)

MI

分钟(00-59)

SS

秒(00-59)

MS

毫秒(000-999)

US

微秒(000000-999999)

AM

正午标识(大写)

Y,YYY

带逗号的年(4和更多位)

YYYY

年(4和更多位)

YYY

年的后三位

YY

年的后两位

Y

年的最后一位

MONTH

全长大写月份名(空白填充为9字符)

Month

全长混合大小写月份名(空白填充为9字符)

month

全长小写月份名(空白填充为9字符)

MON

大写缩写月份名(3字符)

Mon

缩写混合大小写月份名(3字符)

mon

小写缩写月份名(3字符)

MM

月份号(01-12)

DAY

全长大写日期名(空白填充为9字符)

Day

全长混合大小写日期名(空白填充为9字符)

day

全长小写日期名(空白填充为9字符)

DY

缩写大写日期名(3字符)

Dy

缩写混合大小写日期名(3字符)

dy

缩写小写日期名(3字符)

DDD

一年里的日子(001-366)

DD

一个月里的日子(01-31)

D

一周里的日子(1-7;周日是1)

W

一个月里的周数(1-5)(第一周从该月第一天开始)

WW

一年里的周数(1-53)(第一周从该年的第一天开始)

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

模式

描述

9

带有指定数值位数的值

0

带前导零的值

.(句点)

小数点

,(逗号)

分组(千)分隔符

PR

尖括号内负值

S

带符号的数值

L

货币符号

D

小数点

G

分组分隔符

MI

在指明的位置的负号(如果数字 < 0)

PL

在指明的位置的正号(如果数字 > 0)

SG

在指明的位置的正/负号

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

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

操作符

例子

结果

+

date '2001-09-28' + integer '7'

date '2001-10-05'

+

date '2001-09-28' + interval '1 hour'

timestamp '2001-09-28 01:00'

+

date '2001-09-28' + time '03:00'

timestamp '2001-09-28 03:00'

+

interval '1 day' + interval '1 hour'

interval '1 day 01:00'

+

timestamp '2001-09-28 01:00' + interval '23 hours'

timestamp '2001-09-29 00:00'

+

time '01:00' + interval '3 hours'

time '04:00'

-

- interval '23 hours'

interval '-23:00'

-

date '2001-10-01' - date '2001-09-28'

integer '3'

-

date '2001-10-01' - integer '7'

date '2001-09-24'

-

date '2001-09-28' - interval '1 hour'

timestamp '2001-09-27 23:00'

-

time '05:00' - time '03:00'

interval '02:00'

-

time '05:00' - interval '2 hours'

time '03:00'

-

timestamp '2001-09-28 23:00' - interval '23 hours'

timestamp '2001-09-28 00:00'

-

interval '1 day' - interval '1 hour'

interval '23:00'

-

timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'

interval '1 day 15:00'

*

interval '1 hour' * double precision '3.5'

interval '03:30'

/

interval '1 hour' / double precision '1.5'

interval '00:40'

2. 日期/时间函数:

函数

返回类型

描述

例子

结果

age(timestamp, timestamp)

interval

减去参数,生成一个使用年、月的"符号化"的结果

age('2001-04-10', timestamp '1957-06-13')

43 years 9 mons 27 days

age(timestamp)

interval

从current_date减去得到的数值

age(timestamp '1957-06-13')

43 years 8 mons 3 days

current_date

date

今天的日期

current_time

time

现在的时间

current_timestamp

timestamp

日期和时间

date_part(text, timestamp)

double

获取子域(等效于extract)

date_part('hour', timestamp '2001-02-16 20:38:40')

20

date_part(text, interval)

double

获取子域(等效于extract)

date_part('month', interval '2 years 3 months')

3

date_trunc(text, timestamp)

timestamp

截断成指定的精度

date_trunc('hour', timestamp '2001-02-16 20:38:40')

2001-02-16 20:00:00+00

extract(field from timestamp)

double

获取子域

extract(hour from timestamp '2001-02-16 20:38:40')

20

extract(field from interval)

double

获取子域

extract(month from interval '2 years 3 months')

3

localtime

time

今日的时间

localtimestamp

timestamp

日期和时间

now()

timestamp

当前的日期和时间(等效于 current_timestamp)

timeofday()

text

当前日期和时间

3. EXTRACT,date_part函数支持的field:

描述

例子

结果

CENTURY

世纪

EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');

20

DAY

(月分)里的日期域(1-31)

EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');

16

DECADE

年份域除以10

EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');

200

DOW

每周的星期号(0-6;星期天是0) (仅用于timestamp)

EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

5

DOY

一年的第几天(1 -365/366) (仅用于 timestamp)

EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');

47

HOUR

小时域(0-23)

EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');

20

MICROSECONDS

秒域,包括小数部分,乘以 1,000,000。

EXTRACT(MICROSECONDS from TIME '17:12:28.5');

28500000

MILLENNIUM

千年

EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');

3

MILLISECONDS

秒域,包括小数部分,乘以 1000。

EXTRACT(MILLISECONDS from TIME '17:12:28.5');

28500

MINUTE

分钟域(0-59)

EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');

38

MONTH

对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11)

EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');

2

QUARTER

该天所在的该年的季度(1-4)(仅用于 timestamp)

EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');

1

SECOND

秒域,包括小数部分(0-59[1])

EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');

40

WEEK

该天在所在的年份里是第几周。

EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');

7

YEAR

年份域

EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');

2001

4. 当前日期/时间:

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

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME (precision)

CURRENT_TIMESTAMP (precision)

LOCALTIME

LOCALTIMESTAMP

LOCALTIME (precision)

LOCALTIMESTAMP (precision)

九、序列操作函数:

序列对象(也叫序列生成器)都是用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才开始递增该序列。

对于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;

i

---

1

2

3

(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

----------

5

(1 row)

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

least

-------

1

(1 row)

十一、数组函数和操作符:

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

操作符

描述

例子

结果

=

等于

ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]

t

<>

不等于

ARRAY[1,2,3] <> ARRAY[1,2,4]

t

<

小于

ARRAY[1,2,3] < ARRAY[1,2,4]

t

>

大于

ARRAY[1,4,3] > ARRAY[1,2,4]

t

<=

小于或等于

ARRAY[1,2,3] <= ARRAY[1,2,3]

t

>=

大于或等于

ARRAY[1,4,3] >= ARRAY[1,4,3]

t

||

数组与数组连接

ARRAY[1,2,3] || ARRAY[4,5,6]

{1,2,3,4,5,6}

||

数组与数组连接

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]

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

||

元素与数组连接

3 || ARRAY[4,5,6]

{3,4,5,6}

||

元素与数组连接

ARRAY[4,5,6] || 7

{4,5,6,7}

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

函数

返回类型

描述

例子

结果

array_cat(anyarray, anyarray)

anyarray

连接两个数组

array_cat(ARRAY[1,2,3], ARRAY[4,5])

{1,2,3,4,5}

array_append(anyarray, anyelement)

anyarray

向一个数组末尾附加一个元素

array_append(ARRAY[1,2], 3)

{1,2,3}

array_prepend(anyelement, anyarray)

anyarray

向一个数组开头附加一个元素

array_prepend(1, ARRAY[2,3])

{1,2,3}

array_dims(anyarray)

text

返回一个数组维数的文本表示

array_dims(ARRAY[[1,2,3], [4,5,6]])

[1:2][1:3]

array_lower(anyarray, int)

int

返回指定的数组维数的下界

array_lower(array_prepend(0, ARRAY[1,2,3]), 1)

0

array_upper(anyarray, int)

int

返回指定数组维数的上界

array_upper(ARRAY[1,2,3,4], 1)

4

array_to_string(anyarray, text)

text

使用提供的分隔符连接数组元素

array_to_string(ARRAY[1, 2, 3], '~^~')

1~^~2~^~3

string_to_array(text, text)

text[]

使用指定的分隔符把字串拆分成数组元素

string_to_array('xx~^~yy~^~zz', '~^~')

{xx,yy,zz}

十二、系统信息函数:

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

名字

返回类型

描述

current_database()

name

当前数据库的名字

current_schema()

name

当前模式的名字

current_schemas(boolean)

name[]

在搜索路径中的模式名字

current_user

name

目前执行环境下的用户名

inet_client_addr()

inet

连接的远端地址

inet_client_port()

int

连接的远端端口

inet_server_addr()

inet

连接的本地地址

inet_server_port()

int

连接的本地端口

session_user

name

会话用户名

pg_postmaster_start_time()

timestamp

postmaster启动的时间

user

name

current_user

version()

text

PostgreSQL版本信息

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

名字

描述

可用权限

has_table_privilege(user,table,privilege)

用户是否有访问表的权限

SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER

has_table_privilege(table,privilege)

当前用户是否有访问表的权限

SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER

has_database_privilege(user,database,privilege)

用户是否有访问数据库的权限

CREATE/TEMPORARY

has_database_privilege(database,privilege)

当前用户是否有访问数据库的权限

CREATE/TEMPORARY

has_function_privilege(user,function,privilege)

用户是否有访问函数的权限

EXECUTE

has_function_privilege(function,privilege)

当前用户是否有访问函数的权限

EXECUTE

has_language_privilege(user,language,privilege)

用户是否有访问语言的权限

USAGE

has_language_privilege(language,privilege)

当前用户是否有访问语言的权限

USAGE

has_schema_privilege(user,schema,privilege)

用户是否有访问模式的权限

CREAT/USAGE

has_schema_privilege(schema,privilege)

当前用户是否有访问模式的权限

CREAT/USAGE

has_tablespace_privilege(user,tablespace,privilege)

用户是否有访问表空间的权限

CREATE

has_tablespace_privilege(tablespace,privilege)

当前用户是否有访问表空间的权限

CREATE

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

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

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

名字

描述

应用类型

pg_table_is_visible(table_oid)

该表/视图是否在搜索路径中可见

regclass

pg_type_is_visible(type_oid)

该类/视图型是否在搜索路径中可见

regtype

pg_function_is_visible(function_oid)

该函数是否在搜索路径中可见

regprocedure

pg_operator_is_visible(operator_oid)

该操作符是否在搜索路径中可见

regoperator

pg_opclass_is_visible(opclass_oid)

该操作符表是否在搜索路径中可见

regclass

pg_conversion_is_visible(conversion_oid)

转换是否在搜索路径中可见

regoperator

注:以上函数均返回boolean类型,所有这些函数都需要对象 OID 标识作为检查的对象。

postgres=#

SELECT pg_table_is_visible('testtable'::regclass);

pg_table_is_visible

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

t

(1 row)

4. 系统表信息函数:

名字

返回类型

描述

format_type(type_oid,typemod)

text

获取一个数据类型的SQL名称

pg_get_viewdef(view_oid)

text

为视图获取CREATE VIEW命令

pg_get_viewdef(view_oid,pretty_bool)

text

为视图获取CREATE VIEW命令

pg_get_ruledef(rule_oid)

text

为规则获取CREATE RULE命令

pg_get_ruledef(rule_oid,pretty_bool)

text

为规则获取CREATE RULE命令

pg_get_indexdef(index_oid)

text

为索引获取CREATE INDEX命令

pg_get_indexdef(index_oid,column_no,pretty_bool)

text

为索引获取CREATE INDEX命令, 如果column_no不为零,则是只获取一个索引字段的定义

pg_get_triggerdef(trigger_oid)

text

为触发器获取CREATE [CONSTRAINT] TRIGGER

pg_get_constraintdef(constraint_oid)

text

获取一个约束的定义

pg_get_constraintdef(constraint_oid,pretty_bool)

text

获取一个约束的定义

pg_get_expr(expr_text,relation_oid)

text

反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系

pg_get_expr(expr_text,relation_oid, pretty_bool)

text

反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系

pg_get_userbyid(roleid)

name

获取给出的ID的角色名

pg_get_serial_sequence(table_name,column_name)

text

获取一个serial或者bigserial字段使用的序列名字

pg_tablespace_databases(tablespace_oid)

setof oid

获取在指定表空间(OID表示)中拥有对象的一套数据库的OID的集合

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

十三、系统管理函数:

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

名字

返回类型

描述

current_setting(setting_name)

text

当前设置的值

set_config(setting_name,new_value,is_local)

text

设置参数并返回新值

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. 数据库对象尺寸函数:

名字

返回类型

描述

pg_tablespace_size(oid)

bigint

指定OID代表的表空间使用的磁盘空间

pg_tablespace_size(name)

bigint

指定名字的表空间使用的磁盘空间

pg_database_size(oid)

bigint

指定OID代表的数据库使用的磁盘空间

pg_database_size(name)

bigint

指定名称的数据库使用的磁盘空间

pg_relation_size(oid)

bigint

指定OID代表的表或者索引所使用的磁盘空间

pg_relation_size(text)

bigint

指定名称的表或者索引使用的磁盘空间。这个名字可以用模式名修饰

pg_total_relation_size(oid)

bigint

指定OID代表的表使用的磁盘空间,包括索引和压缩数据

pg_total_relation_size(text)

bigint

指定名字的表所使用的全部磁盘空间,包括索引和压缩数据。表名字可以用模式名修饰。

pg_size_pretty(bigint)

text

把字节计算的尺寸转换成一个人类易读的尺寸单位

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

名字

返回类型

描述

pg_relation_filenode(relation regclass)

oid

获取指定对象的文件节点编号(通常为对象的oid值)。

pg_relation_filepath(relation regclass)

text

获取指定对象的完整路径名。

mydatabase=# selectpg_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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值