數(shù)字類型

在 PostgreSQL 中，decimal 和 numeric 是相同的:

create table public.test(
  f1 int4, -- 32 位, 相當(dāng)于 Java 中的 int
  f2 real, -- 相當(dāng)于 float, 而 PostgreSQL 中的 float 則會(huì)自動(dòng)轉(zhuǎn)化為 double precision
  f3 decimal(5, 3) -- 5: 全部位數(shù), 3: 小數(shù)部分位數(shù), 如 33.123
)

除了基本的數(shù)字類型外，PostgreSQL 中還提供了自增的數(shù)據(jù)類型：

create table public.test(
  id1 smallserial, -- int2 自增
  id2 serial, -- int4 自增
  id3 bigserial -- int8 自增
)

數(shù)字類型函數(shù)：

• 四則運(yùn)算

select
     (5 + 3) as f1, -- 8
     (5 - 3) as f2, -- 2
     (5 * 3) as f3, -- 15
     (5 / 3) as f4; -- 1

• 取余函數(shù)

select mod(9, 2); -- 1

• 四舍五入函數(shù)

select round(11.2), -- 11
     round(11.6); -- 12

字符類型

在 PostgreSQL 中，varchar(n) = character varying(n)，char(n) = character(n).

字符類型函數(shù)：

• 字符長(zhǎng)度函數(shù)

select char_length('abcd'); -- 4

• 字符在字符串的位置

select position('ab' in 'alabb'); -- 3, 下標(biāo)從 1 開始

• 提取子串

select substring('abcdefg' from 3 for 2); -- cd, 從 3 開始, 截取兩個(gè)

• 切分字符串

select split_part('aladdin.im', '.', 1); -- aladdin, 原字符串, 分隔符, 第幾個(gè)字段

時(shí)間類型

Demo：

select now()::timestamp; -- 2019-03-22 06:32:57.754700
select now()::timestamp with time zone; -- 2019-03-22 06:35:43.484050
select now()::date; -- 2019-03-22
select now()::time; -- 06:37:26
select now()::time with time zone; -- 06:38:18.979388
select now() + interval '1 day'; -- 明天的時(shí)間
-- interval '1 day' = 將 1 day 轉(zhuǎn)換成時(shí)間間隔

四則運(yùn)算：

• 加 & 減

select now() + interval '1 day';
select now() + interval '1 hour';
select now() - interval '1 second';

• 乘

select interval '1 second' * 100; -- 0 years 0 mons 0 days 0 hours 1 mins 40.00 secs

• 除

select interval '1 hour' / 3; -- 0 years 0 mons 0 days 0 hours 20 mins 0.00 secs

常用函數(shù)：

• 抽取函數(shù)

select extract(year from now()); -- 2019
select extract(week from now()); -- 12, 一年中的第幾周
select extract(doy from now()); -- 81, 一年中的第幾天

布爾類型

給布爾類型賦值：

insert into table3 values (true);
insert into table3 values ('true');
insert into table3 values ('t');
insert into table3 values ('1'); -- true

網(wǎng)絡(luò)地址類型

Demo:

select '192.168.0.1'::cidr; -- 192.168.0.1/32
select '192.168.0.1'::inet; -- 192.168.0.1
select '192.168.0.1/16'::inet; -- 192.168.0.1/16
select '00-01-6C-06-A6-29'::macaddr; -- 00:01:6c:06:a6:29

常用函數(shù)：

• 獲取主機(jī)

select host(inet '192.168.0.1/16'); -- 192.168.0.1

• 轉(zhuǎn)換成文本

select text(inet '192.168.0.1/16'); -- 192.168.0.1/16

• 獲取子掩碼

select netmask(inet '192.168.0.1/16'); -- 255.255.0.0

數(shù)組類型

定義：

create table table4(
  intarr int4[],
  textarr text[]
);

插入：

-- 方式一
insert into 
  public.table4(intarr, textarr) 
  values
         ('{1, 2, 3}', '{"a", "b", "c"}');

-- 方式二
insert into 
  public.table4(intarr, textarr) 
  values 
         (array[1, 2, 3], array['a', 'b', 'c']);

查詢：

-- 下標(biāo)從 1 開始，而非 0
select intarr[1], intarr[2]
from table4;

數(shù)組元素增刪改：

-- 增加
select array_append(intarr, 4) from table4; -- {1， 2， 3， 4}
-- 刪除
select array_remove(intarr, 2) from table4;
-- 上面并未更改真正的數(shù)據(jù)

-- 增加
update table4 set intarr = array_append(intarr, 4); -- where ...
-- 更新
update table4 set intarr[2] = 8; -- where ...

數(shù)組函數(shù)：

• 數(shù)組維度

select array_ndims(intarr) from table4; -- 1

• 數(shù)組長(zhǎng)度

select array_length(intarr, 1) from table4; -- array_length(arr, dim), dim: 維度

• 元素位置

select array_position(intarr, 8) from table4; -- 下標(biāo)從一開始

• 元素替換

select array_replace(intarr, 8, 2) from table4; -- 將 8 替換成 2

范圍類型

Demo：

select int4range(4, 9); -- [4,9)
select daterange('2018-9-1', '2019-9-1'); -- [2018-09-01,2019-09-01)

范圍操作符：

select int4range(1, 10) @> 4; -- true
select int4range(1, 10) @> 15; -- false

范圍類型函數(shù)：

• 上下限

select lower(int4range(2, 6)); -- 2
select upper(int4range(2, 6)); -- 6

Json 類型

創(chuàng)建：

create table public.table1(
  id int4,
  info json
);

插入：

insert into public.table1 values (1001, '{"name": "aladdin", "addr": "in china"}');

查詢：

select info ->> 'name' as name from table1; -- aladdin
select info -> 'name' as name from table1; -- "aladdin"

Jsonb 類型

Jsonb 與 Json 使用上完全相同，但是 Json 類型會(huì)以文本的形式存入，Jsonb 會(huì)先對(duì) Json 串進(jìn)行編譯，然后存入。

由于 Json 格式不需要編譯就可以直接以文本的形式存入，所以插入速度更快，但是每次查詢都需要對(duì) Json 進(jìn)行解析，查詢速度較慢；Jsonb 格式恰恰相反，Jsonb 格式會(huì)先將 Json 串進(jìn)行編譯然后存儲(chǔ)，所以插入速度較慢，查詢速度較快。

如果做數(shù)據(jù)分析，一般都會(huì)選用 Jsonb 格式。

創(chuàng)建：

create table public.table2(
  id int4,
  info jsonb
);

插入：

insert into public.table2 values (1001, '{"name": "aladdin", "addr": "in china"}');

查詢：

select info ->> 'name' as name from table2; -- aladdin
select info -> 'name' as name from table2; -- "aladdin"

鍵值增刪改操作：

-- " || " 表示增加 / 更新
select '{"name": "aladdin", "addr": "in china"}'::jsonb || '{"hobby": "computer games"}'::jsonb;
-- 
select '{"name": "aladdin", "addr": "in china"}'::jsonb || '{"name": "aladdinxyz"}'::jsonb;

-- " - " 表示刪除
select '{"name": "aladdin", "addr": "in china"}'::jsonb - 'addr';

Json 與 Jsonb 常用函數(shù)：

• 組成結(jié)果集

select * from json_each_text('{"name": "aladdin", "addr": "in china"}');
select * from json_each('{"name": "aladdin", "addr": "in china"}'); -- value 帶 "" 號(hào)

• 數(shù)據(jù)集轉(zhuǎn) Json

select row_to_json(table3) from table3;

數(shù)據(jù)類型的轉(zhuǎn)換

• CAST 轉(zhuǎn)換：

select cast(varchar'123' as int4);

• 通過 " :: " 進(jìn)行轉(zhuǎn)換：

select 1::int4, 3/2::double precision;

• 轉(zhuǎn)換函數(shù)：

-- timestamp to char
select to_char(current_timestamp, 'yyyy:MM:dd');

-- 分鐘為 mi 而不是 mm
select to_char(interval '10h 5m 20s', 'HH24:mi:ss');

select to_char(123, '0999'); -- 0123
select to_char(123.5, '999D99'); -- 123.50

-- char to date
select to_date('2018-9-13', 'yyyy-MM-dd');

-- char to timestamp
select to_timestamp('2018-9-13', 'yyyy-MM-dd');