pgsql处理文档类型数据_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');

一、逻辑操作符：

常用的逻辑操作符有：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</