PostgreSQL数据类型

已于 2022-01-28 16:40:17 修改
阅读量4.7k 收藏 19
点赞数 8
分类专栏： PostgreSQL 文章标签： postgresql sql 数据类型
于 2021-05-11 09:51:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36213352/article/details/116641840
版权

文章目录

PostgreSQL数据类型

PostgreSQL支持最大字段大小为1GB

数字类型

类型名称存储长度描述范围
smallint2字节小范围整数类型-32768到+32767
integer4字节整数类型-2 147 483 648到+2 147 483 647
bigint8字节大范围整数类型-9 223 372 036 854 775 808到+9 223 372 036 854 775 807
decimal可变用户指定精度小数点前131 072位；小数点后16 383位
numeric可变用户指定精度小数点前131 072位；小数点后16 383位
real4字节变长，不精确6位十进制精度
doblue precision8字节变长，不精确15位十进制精度
smallserial2字节smallint自增序列1到+32767
serial4字节integer自增序列1到+2 147 483 647
bigserial8字节bigint自增序列1到+9 223 372 036 854 775 807

从性能上考虑不建议使用numeric数据类型

# 创建表
CREATE TABLE test_serial(id serial,flag text);

# 插入数据
INSERT INTO test_serial(flag)VALUES('a');
  • 数字类型操作符和数字函数
SELECT 1+2,2*3,4/2,8%3;

# 按模取余
SELECT mod(8,3);

# 四舍五入
SELECT round(10.2),round(10.9);

# 返回大于或等于给出参数的最小整数
SELECT ceil(3.6),ceil(-3.6);

# 返回小于或等于给出参数的最小整数
SELECT floor(3.6),floor(-3.6);

字符类型

字符类型名称描述
varcher(n),character varying(n)变长，自负最大数有限制
char(n),character(n)定长，字符数没达到
text变长，无长度限制
# 创建表
CREATE TABLE test_char(col1 varchar(4),col2 character(4));

# 插入数据
INSERT INTO test_char(col1,col2)VALUES('a','a');

# 查询字符串字符数
SELECT char_length(col1),char_length(col2) FROM test_char;

# 查看字段实际占用的物理空间大小(字符串占用的字符数)
SELECT octet_length(col1),octet_length(col2) FROM test_char;
  • 字符类型函数
# 计算字符串中的字符数
SELECT char_length('abcd');

# 计算字符串占用字节数
SELECT octet_length('abcd');

# 指定字符在字符串中的位置
SELECT position('ab'in 'abcd');

# 提取字符串中的子串
SELECT substring('francs' from 3 for 4);

# 拆分字符串
split_part(string text,delimiter text,field int)
SELECT split_part('abc@def1@nb','@',2); # return def1

时间/日期类型

字符类型名称存储长度描述
timestamp[§] [without time zone]8字节包括日期和时间，不带时区，简写成timestamp
timestamp[§] [with time zone]8字节包括日期和时间，带时区，简写成timestampz
date4字节日期，但不包括一天中的时间
time[§] [without time zone]8字节一天中的时间，不包含日期，不带时区
time[§] [with time zone]12字节一天中的时间，不包含日期，带时区
interval[fields] [§]16字节时间间隔
# 显示系统当前时间
SELECT now();

# 转换成date格式
SELECT now()::date;

# 转换成time with time zone
SELECT now()::time with time zone;

# interval 指时间间隔，时间间隔单位可以是hour、day、month、year等
SELECT now(),now()+interval'1 day';

时间类型中的§是指时间精度，具体指秒后面小数点保留的位数，如果没有声明精度默认为6

# 精度设置为0
SELECT now(),now()::timestamp(0);
  • 时间/日期类型操作符
# 日期相加
SELECT date '2017-07-29'+interval'1 day';

# 日期相减
SELECT date '2017-07-29'-interval'1 hour';

# 日期相乘
SELECT 100* interval '1 second';

# 日期相除
SELECT interval '1 hour' / double precision '3';
  • 时间/日期类型常用函数
# 显示当前时间
SELECT current_date, current_time;

EXTRACT函数，可以从日期、时间数据类型中抽取年、月、日、时、分、秒信息

EXTRACT(field FROM source)

field值可以为century、year、month、day、hour、miniute、second等，source 类型为timestamp、time、interval的值表达式

# 取年份SELECT EXTRACT(year FROM now());# 取小时、分钟SELECT EXTRACT(hour FROM now()),EXTRACT(minute FROM now());#取秒SELECT EXTRACT(second FROM now());# 取当前日期所在年份中的第几周SELECT EXTRACT(week FROM now());# 当天属于当年的第几天SELECT EXTRACT(doy FROM now());# 当天属于当月的第几天SELECT EXTRACT(day FROM now());

布尔类型

字符类型名称存储长度描述
boolean1字节状态为true或false

true状态的有效值可以TRUE、t、true、y、yes、on、1；

false状态的有效值为FALISE、f、false、n、no、off、0；

可以插入NULL值

网络地址类型

字符类型名称存储长度描述
cidr7或19字节IPv4和IPv6网络
inet7或19字节IPv4和IPv6网络
macaddr6字节MAC地址
Macaddr88字节MAC地址（EUI-64格式）
SELECT '192.168.1.100'::cidr;SELECT '192.168.1.100/32'::inet;

cidr类型对IP地址和子网掩码合法性进行检查，而inet不会；

cidr比inet网络类型更严谨；

  • 网络地址操作符
操作符描述举例
<小于inet ‘192.168.1.5’ < inet ‘192.168.1.6’
<=小于等于inet ‘192.168.1.5’ <= inet ‘192.168.1.5’
=等于inet ‘192.168.1.5’ = inet’192. 168.1.5’
>=大于等于inet ‘192.168.1.5’>= inet’192.168.1.5’
>大于inet ‘192.168.1.5’> inet ‘192.168.1.4’
<>不等于inet ‘192.168.1.5’<> inet ‘192.168.1.4’
<<被包含inet ‘192.168.1.5’ << inet ‘192.168.1/24’
<<=被包含或等于inet ‘192.168.1/24’ <<= inet ‘192.168.1/24’
>>包含inet ‘192.168.1/24’>> inet ‘192.168.1.5’
>>=包含或等于inet ‘192.168.1/24’>>= inet ‘192.168.1/24’
&&包含或被包含inet ‘192.168.1/24’ && inet ‘192.168.1.80/28’
～按位取反~ inet ‘192.168.1.6’
&按位与inet ‘192.168.1.6’ & inet ‘0.0.0.255’
｜按位或inet ‘192.168.1.6’ I inet ‘0.0.0.255’
+加inet ‘192.168.1.6’ + 25
-减inet ‘192.168.1.43’ - 36
-减inet ‘192.168.1.43’ - inet ‘192. 168.1.19’
  • 网络地址函数
# 取IP地址，返回文本格式SELECT host(cidr '192.168.1.0/24');# 去IP地址和网络掩码，返回文本格式SELECT text(cidr '192.168.1.0/24');# 取网络地址子网掩码，返回文本格式SELECT netwask(cidr )

数组类型

创建表时在字段数据类型后面加方括号“[]”

# 创建表CREATE TABLE test_array1(	id  integer,  array_i integer[],  array_t text[]);# 插入数据INSERT INTO test_array1(id,array_i,array_t)VALUES(1,'{1,2,3}','{"a","b","c"}');INSERT INTO test_array1(id,array_i,array_t)VALUES(2,array[1,2,3],array['d','e','f']);# 查询数据SELECT * FROM test_array1;
  • 查询数组元素
# 查询数组全部元素值SELECT array_i FROM test_array1 WHERE id=1;# 数组引用通过方括号“[]”方式，数组下标写在方括号内，编号范围为1到nSELECT array_i[1],array_[3] FROM test_array1 WHERE id=1;
  • 数组元素的追加、删除、更新操作
# 向数组末端追加一个元素SELECT array_append(array[1,2,3],4);SELECT array[1,2,3] || 4;# 数组元素删除,删除全部的2SELECT array[1,2,3,4],array_remove(array[1,2,2,4],2);# 数组元素修改UPDATE test_array1 SET array_i[3]=4 WHERE id=1;UPDATE test_array1 SET array_i=array[7,8,9] WHERE id=1;
  • 数组操作符
操作符描述举例结果
=等于ARRAYI1.1,2.1.31]: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,4,3] @> ARRAY[3,1]t
<@被包含ARRAY[2,7] <@ ARRAY[1,7,4,2,6]t
&&重叠（具有公共元素）ARRAY[1,4,3] && ARRAY[2,1]t
||数组和数组串接ARRAY[1,2,3]II ARRAY[4,5,6]{1,2,3,4,5,6}
||数组和数组串接ARRAY[1,2,3] II ARRAY[[4,5,6],[7,8,9]]{1,2,3},{4,5,6},{7,8,9}}
||元素和数组串接3 II ARRAY[4,5,6]{3,4,5,6}
||数组和元素串接ARRAY[4,5,6] II 7{4,5,6,7}
  • 数组函数
# 添加元素或删除元素SELECT array_append(array[1,2],3),array_remove(array[1,2],2);# 获取数组维度SELECT array_ndims(array[1,2]);# 获取数组长度SELECT array_length(array[1,2],1);# 返回数组中某一个数组元素第一次出现的位置SELECT array_position(array['a','b','c','d'],'d');# 数组元素替换SELECT array_replace(array[1,2,5,4],5,10);# 将数组元素输出到字符串SELECT array_to_string(array[1,2,null,3],',','10');

范围类型

字符类型名称描述
int4rangeinteger范围类型
int8rangebigint范围类型
numranagenumeric范围类型
tsrange不带时区的timestamp范围类型
tstzranage带时区的timestamp范围类型
daterangedate范围类型
# [1,5)SELECT int4ranage(1,5);# [2017-07-01,2017-07-30)SELECT daterange('2017-07-01','2017-07-30');# 指定上下边界[1,4)SELECT int4range(1,3,'[]');

范围类型标准的边界模式，即下界包含同时上界排除

  • 范围类型操作符
# 包含元素操作符SELECT int4ranage(4,7) @>4;# 包含范围操作符SELECT int4range(4,7) int4range(4,6);# 等于操作符SELECT int4range(4,7)=int4range(4,6,'[]');
  • 范围类型函数
# 取范围下界SELECT lower(int4range(1,10));# 取范围上界SELECT upper(int4range(1,10));# 范围是否为空SELECT isempty(int4range(1,10));
  • 给范围类型创建索引

范围类型数据支持创建GiST索引，GiST索引支持的操作符有“=”、“&&”、“<@”、“@>”、“<<”、“>>”、“-|-”、“&<”、“&>”等

CREATE INDEX idx_ip_address_range ON ip_address USING gist(ip_range);

json/jsonb类型

SELECT '{"a":1,"b":2}'::json;# 创建表CREATE TABLE test_json1 (id serial primary key,name json);# 插入数据INSERT INTO test_json1(name)VALUES('{"col1":1,"col2":"francs","col3":"male"}');INSERT INTO test_json1(name)VALUES('{"col1":1,"col2":"fp","col3":"female"}');# 查询数据SELECT * FROM test_json1;
  • 查询json数据
# 查询json数据的键值SELECT name -> 'col2' FROM test_json1 WHERE id=1;# 以文本格式返回json字段键值SELECT name ->> 'col2' FROM test_json1 WHERE id=1;
  • jsonb与json差异

json存储格式为文本，jsonb存储格式为二进制；

json写入比jsonb快，但检索比jsonb慢；

json输出的键的顺序和输入不一样，json的输出键的顺序和输入完全一样；

jsonb类型会去掉输入数据中键值的空格；

jsonb会删除重复的键，仅保留最后一个；

SELECT '{"bar":"baz","balance":7.77,"active":false}'::jsonb;
  • jsonb与json操作符
# 以文本格式返回json字段键值SELECT name ->> 'col2' FROM test_json1 WHERE id=1;# 字符串是否作为顶层值SELECT '{"a":1,"b":2}'::jsonb ? 'a';# 删除json数据的键/值SELECT '{"a":1,"b":2}'::jsonb - 'a';
  • jsonb与json函数
# 扩展最外层的json对象成为一组键/值结果集SELECT * FROM json_each('{"a":"foo","b":"bar"}');# 以文本形式返回结果SELECT * FROM json_each_text('{"a":"foo","b":"bar"}');# 将行作为json对象返回SELECT row_to_json(test_copy)FROM test_copy WHERE id=1;# 返回最外层的json对象中的键的集合SELECT * FROM json_object_keys('{"a":"foo","b":"bar"}');
  • jsonb键/值的追加、删除、更新
# 增减sex键/值SELECT '{"name":"francs","age":"31"}'::jsonb||'{"sex":"male"}'::jsonb;# 删除键/值SELECT '{"name":"James","email":"james@localhost"}'::jsonb - 'email';# 删除嵌套contact中fax键/值SELECT '{"name":"James","contact":{"phone":"01234 567890","fax":"01987 543210"}}'::jsonb #- '{contact,fax}'::text[];# 删除嵌套aliases中的位置为1的键/值SELECT '{"name":"James","aliases":["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];# 键/值更新SELECT '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb;SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);

数据类型转换

PostgreSQL数据类型转换主要有三种方式：通过格式化函数、CAST函数、::操作服务

  • 通过格式函数进行转换
函数返回类型描述实例
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(numeric,text)text把数字转换成字符串to_char(-125.8,‘999D99S’)
to_date(text,text)date把字符串转换成日期to_date(‘05 Dec 2000’,‘DD Mon YYYY’)
to_number(text,text)numeric把字符串转换成数字to_number(‘12,454.8-’,‘99G999D9s’)
to_timestamp(text,text)timestamp with time zone把字符串转换成时间戳to_timestamp(‘05 Dec 2000’,‘DD Mon YYYY’)
  • 通过CAST函数进行转换
# 将varchar转换成textSELECT CAST(varchar'123' as text);# 将varchar转换成int4SELECT CAST(varchar'123' as int4);
  • 通过::操作符进行转换
SELECT 1::int4, 3/2::numeric;SELECT oid,relname FROM pg_class WHERE relname='test_json1';SELECT attname FROM pg_attribute WHERE attrelid='16509' AND attnum >0;SELECT attname FROM pg_attribute WHERE attrelid='test_json1'::regclass AND attnum >0;
孙霸天
关注
  • 8
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
订阅专栏
PostgreSQL 数据类型
12-16
PostgreSQL 数据类型 本章节，我们将讨论 PostgreSQL 的数据类型，数据类型是我们再创建表的时候为每个字段设置的。 设置数据类型的好处： PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种，下面我们具体来说明。 数值类型 数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。 下表列出了可用的数值类型。 名字 存储长度 描述 范围 smallint 2 字节 小范围整数 -32768 到 +32767
PostgreSQL 数据类型.xmind
03-05
PostgreSQL中数据类型的思维导图，Xmind格式。包括以下分类，数值类型，货币类型，字符类型，日期时间类型，布尔类型，枚举类型，几何类型，网络地址类型，位串类型，文本搜索类型，UUID类型，XML类型，JSON类型，数组类型，复合类型，范围类型等等。
PostgreSQL：所有支持的数据类型及建表语句实例
最新发布
木头大左的博客
04-01 596
哈喽，大家好，我是木头左！
PostgreSQL常用数据类型
11-17
PostgreSQL数据类型，以及与oracle数据类型的对应
postgresql数据类型转换.pdf
09-30
postgresql数据类型转换.pdf
postgreSQL数据类型
12-20
迄今最全最权威的关于PostgreSQL的数据类型介绍，这是扫描国外的《sql技术手册》上的，当然是中文版了，扫描的很清晰，我已按顺序弄到了word里面，是开发postgreSQL数据库必备的参考资料。
PostgreSQL 的 int2 与 smallint 类型
热门推荐
weixin_33860553的博客
10-28 1万+
为什么80%的码农都做不了架构师？>>> ...
PostgreSQL基本数据类型
weixin_48148670的博客
09-03 8034
数值类型 货币类型 money 8字节 money 类型存储带有固定小数精度的货币金额，可以与numeric、int、bigint进行转化。不建议使用浮点数处理货币类型，因为可能存在精度问题。 字符类型 char(n)，character(n)定长，不足补空白 varchar(n) ，character varying(n)变长，有长度限制 text 变长，无长度限制 日期 / 时间 布尔类型 boolean 一字节 ...
pgSQL笔记
weixin_43037331的博客
05-05 312
pgSQL笔记。
PostgreSQL的常见数据类型
香蕉牛奶棒棒槌的博客
03-05 2937
数据类型 1.1 数字类型 PostgreSQL 支持的数字类型有整数类型，用户指定精度类型，浮点型，serial类型。 数字类型列表 smallint，integre，bigint 都是整数类型，存储一定范围的整数，超出范围将会报错。 smallint存储2字节的整数，字段定义时可写成int2。 integer存储4字节整数，支持的数值范围比smallint大，字段定义时可写成int4，是最常...
POSTGRESQL数据类型
07-21
postgresql 数据类型postgresql 数据类型postgresql 数据类型postgresql 数据类型
PostgreSQL教程：数值类型（整型、浮点型、序列、数值的常见操作）
学亮编程手记
09-16 1011
告诉缓存，插入的数据比较多，可以指定告诉缓存，一次性计算出20个后续的值，nextval时，就不可以不去计算，直接去高速缓存拿值，效率会有一内内的提升。数值操作也提供了一些函数，比如pi()，round(数值，位数)，floor()，ceil()PGSQL提供了序列的数据类型，可以在声明表结构时，直接指定序列的类型即可。如果是单独构建序列，再构建表，使用传统方式实现，序列和表就是相对独立的。在drop表之后，序列不会被删除，但是序列会变为不可用的状态。序列大多数的应用，是用作表的主键自增效果。
Postgresql：内置数据类型（data type）
weixin_34242658的博客
07-10 1876
sql standard sqlserver postgresql postgresqlaliases description bigint bigint bigint int8 signed eight-byte integer...
Postgresql数据类型-数字类型
耕耘
11-06 1930
PostgreSQL支持的数字类型有整数类型、用户指定精度类型、浮点类型、serial类型。
PostgreSQL 常用数据类型介绍
ningyuanhuo的专栏
06-08 3127
一、数值类型：    下面是PostgreSQL所支持的数值类型的列表和简单说明：名字存储空间描述范围smallint2 字节小范围整数-32768 到 +32767integer4 字节常用的整数-2147483648 到 +2147483647bigint8 字节大范围的整数-9223372036854775808 到 9223372036854775807decimal变长用户声明精度，精确...
PostgreSQL .数据类型普通数据类型
m0_51063580的博客
11-30 3085
PostgreSQL有着丰富的本地数据类型可用。用户可以使用CREATE TYPE命令为 PostgreSQL增加新的数据类型。 表8.1显示了所有内建的普通数据类型。大部分在“别名”列里列出的可选名字都是因历史原因 被PostgreSQL在内部使用的名字。另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。 表8.1.数据类型 名字 别名 描述 bigint int8 有符号的8字节整数 bigserial serial8 自动增长的8...
postgresql数据库——数据类型总结
weixin_34199335的博客
07-24 2546
postgresql数据库的 数据类型postgresql支持多种数据类型，主要有：整数类型、浮点数类型、任意精度数值、日期时间类型、字符串类型、二进制类型、布尔类型和数组类型等。1、整数类型 postgresql提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需要的存储空间也会越大。 postgresql提供的整数类型有：malli...
mysql数据库与PostgreSQL数据类型的数据类型映射
10-14
MySQL 数据类型与 PostgreSQL 数据类型的映射关系如下： | MySQL 数据类型 | PostgreSQL 数据类型 | | -------------- | -------------------- | | INT | INTEGER | | BIGINT | BIGINT | | FLOAT | REAL | | DOUBLE | DOUBLE PRECISION | | DECIMAL | DECIMAL | | CHAR | CHAR | | VARCHAR | VARCHAR | | TEXT | TEXT | | DATE | DATE | | TIME | TIME | | DATETIME | TIMESTAMP |

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

孙霸天

你的打赏是我不断创作的动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值