数据类型
类型转换
select bit'10101010';select int4'123';select CAST('123' as int4);select numeric'123.2352342';select '123'::int4;select true::BOOLEAN; -- tselect ('f' is true)::BOOLEAN; -- f 数值类型
数值类型 - 序列类型
不同于mysql的自增长，Postgres和Oracle都是序列的方式创建
# 使用SERIAL创建序列testdb=# create table test2 (id SERIAL, title varchar(20));CREATE TABLEtestdb=# \d 关联列表 架构模式 | 名称 | 类型 | 拥有者----------+--------------+--------+---------- public | test1 | 数据表 | postgres public | test2 | 数据表 | postgres public | test2_id_seq | 序列数 | postgres(3 行记录)testdb=# \d test2 数据表 "public.test2" 栏位 | 类型 | Collation | Nullable | Default-------+-----------------------+-----------+----------+----------------------------------- id | integer | | not null | nextval('test2_id_seq'::regclass) title | character varying(20) | | |# 使用CREATE SEQUENCE创建序列testdb=# CREATE SEQUENCE test3_id_seq;CREATE SEQUENCEtestdb=# CREATE TABLE test3 (id int4 NOT NULL DEFAULT nextval('test3_id_seq'), name varchar(20));CREATE TABLEtestdb=# ALTER SEQUENCE test3_id_seq OWNED BY test3.id;ALTER SEQUENCEtestdb=# \d 关联列表 架构模式 | 名称 | 类型 | 拥有者----------+--------------+--------+---------- public | test1 | 数据表 | postgres public | test2 | 数据表 | postgres public | test2_id_seq | 序列数 | postgres public | test3 | 数据表 | postgres public | test3_id_seq | 序列数 | postgres(5 行记录)testdb=# \d test3 数据表 "public.test3" 栏位 | 类型 | Collation | Nullable | Default------+-----------------------+-----------+----------+----------------------------------- id | integer | | not null | nextval('test3_id_seq'::regclass) name | character varying(20) | | | 数值类型 - 货币类型
货币类型可以存储固定小数的货币数目，于浮点数不同，它是完全保证精度的。其输出格式和余灿lc_monetary的设置有关，不同国家的货币输出格式不一样
# 我安装的是中文版本，默认中国postgres=# show lc_monetary; lc_monetary----------------------------------------------------- Chinese (Simplified)_People's Republic of China.936(1 行记录)postgres=# select '100.5'::money; money---------- ￥100.50(1 行记录)# 设置默认美元postgres=# set lc_monetary='C';SETpostgres=# select '100.5'::money; money--------- $100.50(1 行记录)
数值运算
select 4+7; --加select 4-7; --减select 4*7; --乘select 7/3; --除（整数除法讲截断结果）select 6%4; --求模（求余）select 3^3; --幂（指数运算）select |/36.0; --平方根select ||/8.0; --立方根select 5!; --阶乘select !!5; --阶乘（前缀操作）select @-0.5; --绝对值select 31&15; --二进制 ANDselect 31|15; --二进制 ORselect 31#15; --二进制 XORselect ~1; --二进制 NOTselect 1<<8; --二进制 左移
字符串类型
select 'Post' || 'greSQL'; -- 字符串连接SELECT BIT_LENGTH('PostgreSQL'); -- 字符串里的二进制位的个数，位长度SELECT CHAR_LENGTH('PostgreSQL'); -- 字符串李的字符个数 字符个数--SELECT CONVERT('PostgreSQL' using iso_8859_l_to_utf8); -- 使用指定的转换名字改变编码SELECT LOWER('PostgreSQL'); -- 转小写SELECT UPPER('PostgreSQL'); -- 转大写SELECT OCTET_LENGTH('PostgreSQL'); -- 字符串中的字节数SELECT OVERLAY('PostgreSQL' placing 'hello' from 2 for 4); -- 替换子字符串SELECT POSITION('gre' in 'PostgreSQL'); -- 指定的子字符串的位置SELECT SUBSTRING('PostgreSQL' from 2 for 3); -- 抽取子字符串SELECT SUBSTRING('PostgreSQL123' from '\d{1,}$'); -- 抽取匹配POSIX正则表达式的子字符串SELECT SUBSTRING('PostgreSQL123' from '%#"123#"%' for '#'); -- 抽取匹配SQL正则表达式的子字符串，for '#'为设置转移字符SELECT TRIM(' PostgreSQL '); -- 从字符串的开头/结尾两边删除只包含characters中字符（默认是一个空白）最长的字符串SELECT TRIM('xo' from 'oxPostgreSQLxo'); -- 从字符串的开头/结尾两边删除只包含characters中字符（默认是一个空白）最长的字符串 位串
# a必须3位，b可以是少于5的任意位数create table test5(a BIT(3), b BIT VARYING(5));INSERT INTO test(a, b) values(B'101', B'1010');INSERT INTO test(a, b) values(B'101', B'1010');# 操作# 十进制转二进制select 66::bit(8); -- 01000010# 十六进制转二进制select 'x42'::bit(8); -- 01000010# 十六进制转十进制select 'x42'::bit(8)::int; -- 66# 十进制转十六进制select to_hex(66); -- 42
日期/时间类型
postgres=# select date('infinity'); date---------- infinity(1 行记录)postgres=# select current_date; current_date-------------- 2018-11-06(1 行记录)postgres=# select current_time; current_time------------------- 17:04:57.71863+08(1 行记录)postgres=# select current_timestamp; current_timestamp------------------------------- 2018-11-06 17:05:01.710637+08(1 行记录)postgres=# select clock_timestamp(); clock_timestamp------------------------------- 2018-11-06 17:05:05.982679+08(1 行记录)postgres=# select now(); now------------------------------- 2018-11-06 17:05:11.710406+08(1 行记录)postgres=# select date'2018-11-06'; date------------ 2018-11-06(1 行记录)postgres=# select time '16:51:08'; time---------- 16:51:08(1 行记录)postgres=# select time '16:51:08 PST'; time---------- 16:51:08(1 行记录)postgres=# select time with time zone'16:51:08 PST'; timetz------------- 16:51:08-08(1 行记录)postgres=# select time '10:51:08 PM'; time---------- 22:51:08(1 行记录)postgres=# select time '10:51:08 AM'; time---------- 10:51:08(1 行记录)postgres=# select time'10:51:08+8'; time---------- 10:51:08(1 行记录)postgres=# select time'10:51:08+6'; time---------- 10:51:08(1 行记录)postgres=# select time'10:51:08 CCT'; time---------- 10:51:08(1 行记录) ^postgres=# select time with time zone'2018-11-06 10:51:08 Asia/Chongqing'; timetz------------- 10:51:08+08(1 行记录)postgres=# select time with time zone'165408+00'; timetz------------- 16:54:08+00(1 行记录)postgres=# select extract(century from timestamp'2019-11-06 170722'); date_part----------- 21(1 行记录)postgres=# select extract(year from timestamp'2019-11-06 170722'); date_part----------- 2019(1 行记录)postgres=# select extract(decade from timestamp'2019-11-06 170722'); date_part----------- 201(1 行记录)postgres=# select extract(millennium from timestamp'2019-11-06 170722'); date_part----------- 3(1 行记录)postgres=# select extract(month from timestamp'2019-11-06 170722'); date_part----------- 11(1 行记录)postgres=# select extract(quarter from timestamp'2019-11-06 170722'); date_part----------- 4(1 行记录)postgres=# select extract(day from timestamp'2019-11-06 170722'); date_part----------- 6(1 行记录)postgres=# select extract(doy from timestamp'2019-11-06 170722'); date_part----------- 310(1 行记录)postgres=# select extract(hour from timestamp'2019-11-06 170722'); date_part----------- 17(1 行记录) 几何类型
此处不做练习，因为比较复杂，场景也没有不好演示，当然此类型很重要，我们后续有场景的话会专文讲
大家可以自己参考百度下几何类型的操作符和几何类型的函数
网络地址类型
testdb=# select '192.168.10.10/32'::inet; inet--------------- 192.168.10.10(1 行记录)testdb=# select '192.168.10.10'::inet; inet--------------- 192.168.10.10(1 行记录)testdb=# select 'DA01:0000:0000:0000:ABCD:0000:ACBD:0003'::inet; inet--------------------- da01::abcd:0:acbd:3(1 行记录)testdb=# select '192.168.1.100/32'::cidr; cidr------------------ 192.168.1.100/32(1 行记录)testdb=# select '00e04c757d5a'::macaddr; macaddr------------------- 00:e0:4c:75:7d:5a(1 行记录)