您现在的位置是：网站首页> 编程资料编程资料
Postgres bytea类型 转换及查看操作_PostgreSQL_
2023-05-27
580人已围观
简介 Postgres bytea类型 转换及查看操作_PostgreSQL_
一开发表里的列类型为 bytea ，可是它select出来的时候又不是想要的结果：
在postgres配置文件里修改参数
bytea_output = 'escape'
这个默认是hex类型的，修改成escape。
查看的时候还是不是想要的结果：
select encode(data::bytea,'hex') from data_from_target limit 1;
补充：PostgreSQL的数据类型及日常实践笔记
数据类型是编程语言中，在其数据结构上定义的相同值类型的集合以及对该相同值集合的一组操作。而数据类型的值存储离不开变量，因此变量的一个作用就是使用它来存储相同值集的数据类型。数据类型决定了如何将代表这些值的集合存储在计算机的内存中。变量一般遵循先声明后使用的原则。而在数据库中，变量就是字段，用字段来表示一组相同值类型的集合，其实也是先声明后使用的原则。
PostgreSQL支持丰富的数据类型，包括一般的数据类型和非常规的数据类型。一般数据类型包括数值型，货币类型，字符类型，日期类型，布尔类型，枚举类型等，非常规数据类型包括二进制数据类型，几何类型，网络地址类型，位串类型，文本搜索类型，UUID类型,XML类型，JSON类型，数组类型，复合类型，范围类型，Domain类型，OID类型，pg_lsn类型和pseudo-Types类型。
一 数值类型*
1.1整型
PostgreSQL中的整型类型有小整型，整型，大整型，用 smallint,integer,和bigint表示，虽然三个都可以存储相同的数据类型，但是它们各自的存储大小和存储范围却不相同。见下表：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| SMALLINT | 小范围整数，别名为INT2。 | 2字节 | -32,768 - +32,767 |
| INTEGER | 常用的整数，别名为INT4。 | 4字节 | -2,147,483,648 - +2,147,483,647 |
| BIGINT | 大范围的整数，别名为INT8。 | 8字节 | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 |
如下示例所示，在PostgreSQL中，smallint，integer，bigint 数据类型可以使用 int2,int4,int8的扩展写法来标识。
示例：
hrdb=# --创建整型数据类型的表 hrdb=# CREATE TABLE IF NOT EXISTS tab_num(v1 smallint,v2 smallint,v3 int,v4 int,v5 bigint,v6 bigint); CREATE TABLE hrdb=# --表字段注释 hrdb=# COMMENT ON COLUMN tab_num.v1 IS '小整型最小范围'; COMMENT hrdb=# COMMENT ON COLUMN tab_num.v2 IS '小整型最大范围'; COMMENT hrdb=# COMMENT ON COLUMN tab_num.v3 IS '整型最小范围'; COMMENT hrdb=# COMMENT ON COLUMN tab_num.v4 IS '整型最大范围'; COMMENT hrdb=# COMMENT ON COLUMN tab_num.v5 IS '大整型最小范围'; COMMENT hrdb=# COMMENT ON COLUMN tab_num.v6 IS '大整型最大范围'; COMMENT hrdb=# --描述数据类型 hrdb=# \d+ tab_num Table "public.tab_num" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------+-----------+----------+---------+---------+--------------+---------------- v1 | smallint | | | | plain | | 小整型最小范围 v2 | smallint | | | | plain | | 小整型最大范围 v3 | integer | | | | plain | | 整型最小范围 v4 | integer | | | | plain | | 整型最大范围 v5 | bigint | | | | plain | | 大整型最小范围 v6 | bigint | | | | plain | | 大整型最大范围 hrdb=# --插入不同整型的范围数值 hrdb=# INSERT INTO tab_num hrdb-# VALUES (-32768, hrdb(# 32767, hrdb(# -2147483648, hrdb(# 2147483647, hrdb(# -9223372036854775808, hrdb(# 9223372036854775807); INSERT 0 1 hrdb=# --查询结果 hrdb=# SELECT * FROM tab_num; v1 | v2 | v3 | v4 | v5 | v6 --------+-------+-------------+------------+----------------------+--------------------- -32768 | 32767 | -2147483648 | 2147483647 | -9223372036854775808 | 9223372036854775807 (1 row)
如上所示，查询的结果为插入不同整型范围的最值，也说明不同整型范围的边界都是被包括的。在实际生产场景中，SMALLINT、INTEGER和BIGINT类型存储各种范围的数字，也就是整数。当试图存储超出范围以外的数值将会导致错误。
常用的类型是INTEGER，因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有取值范围确定不超过SMALLINT的情况下，才会使用SMALLINT类型。而只有在INTEGER的范围不够的时候才使用BIGINT，因为前者相对要快。
除此之外，创建表也可以使用 int2,int4,int8来代表 smallint,integer,bigint。如下示例所示：
hrdb=# /* hrdb*# smallint,integer,bigint hrdb*# 数据类型分别使用 hrdb*# int2,int4,int8代替 hrdb*# */ hrdb-# CREATE TABLE IF NOT EXISTS tab_numint(v1 int2,v2 int2,v3 int4,v4 int4,v5 int8,v6 int8); CREATE TABLE hrdb=# --描述表定义及数据类型 hrdb=# \d+ tab_numint Table "public.tab_numint" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------+-----------+----------+---------+---------+--------------+------------- v1 | smallint | | | | plain | | v2 | smallint | | | | plain | | v3 | integer | | | | plain | | v4 | integer | | | | plain | | v5 | bigint | | | | plain | | v6 | bigint | | | | plain |
1.2任意精度类型和浮点类型
任意精度类型 numeric、decimal可以存储范围大的数字，存储大小为可变大小，小数点前最多131072位数字，小数点后最多16383位。它可以使用类似浮点类型，将小数精确到保留几位，也可以参与计算可以得到准确的值，但是相比于浮点类型，它的计算比较慢。通常 numeric被推荐使用于存储货币金额或其它要求计算准确的值。详细见下表：
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| NUMERIC[(p[,s])],DECIMAL[(p[,s])] | 精度p取值范围为[1,1000]，标度s取值范围为[0,p]。说明： p为总位数，s为小数位数 | 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。 |
| real | 可变精度 | 4个字节 | 6位小数精度 |
| double precision | 可变精度 | 8个字节 | 15位小数精度 |
示例：任意精度类型
hrdb=# --任意精度类型示例 hrdb=# CREATE TABLE IF NOT EXISTS tab_any_precision(col1 numeric(10,4),col2 decimal(6,4),col3 real,col4 double precision,col5 float4,col6 float8); CREATE TABLE hrdb=# --字段注释 hrdb=# COMMENT ON COLUMN tab_any_precision.col1 IS '表示整数最大位数为6,小数仅保留4位'; COMMENT hrdb=# COMMENT ON COLUMN tab_any_precision.col2 IS '表示整数最大位数为2,小数保留4位'; COMMENT hrdb=# COMMENT ON COLUMN tab_any_precision.col3 IS '表示可变的6位精度的数值类型'; COMMENT hrdb=# COMMENT ON COLUMN tab_any_precision.col4 IS '表示可变的15位精度的数值类型'; COMMENT hrdb=# COMMENT ON COLUMN tab_any_precision.col5 IS '同real'; COMMENT hrdb=# COMMENT ON COLUMN tab_any_precision.col6 IS '同double precision'; COMMENT hrdb=# --查看表定义 hrdb=# \d+ tab_any_precision Table "public.tab_any_precision" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------------------+-----------+----------+---------+---------+--------------+----------------------------------- col1 | numeric(10,4) | | | | main | | 表示整数最大位数为6,小数仅保留4位 col2 | numeric(6,4) | | | | main | | 表示整数最大位数为2,小数保留4位 col3 | real | | | | plain | | 表示可变的6位精度的数值类型 col4 | double precision | | | | plain | | 表示可变的15位精度的数值类型 col5 | real | | | | plain | | 同real col6 | double precision | | | | plain | | 同double precision hrdb=# --插入任意精度测试 hrdb=# INSERT INTO tab_any_precision hrdb-# VALUES(202004.26,20.2004,20.200426,20.203415341535157,20.200426,20.203415341535157); INSERT 0 1 hrdb=# INSERT INTO tab_any_precision hrdb-# VALUES(202004.26105,20.20045,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575); INSERT 0 1 hrdb=# --可以发现col1和col2小数部分可以超过4位，但是读取仅仅保留4位，并遵循四舍五入的原则,如下结果 hrdb=# SELECT * FROM tab_any_precision; col1 | col2 | col3 | col4 | col5 | col6 -------------+---------+---------+------------------+---------+------------------ 202004.2600 | 20.2004 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352 202004.2611 | 20.2005 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352 (2 rows) hrdb=# /* hrdb*# 如果 col1 插入的整数最大位数超过6，将会报错。 hrdb*# 如果 col2 插入的整数最大位数超过2，将会报错。 hrdb*# real 和 double precision 没有限制。 hrdb*# */ hrdb-# INSERT INTO tab_any_precision hrdb-# VALUES(2020042.610,20.2004,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575); ERROR: numeric field overflow DETAIL: A field with precision 10, scale 4 must round to an absolute value less than 10^6. hrdb=# INSERT INTO tab_any_precision hrdb-# VALUES(202004.26105,202.200,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575); ERROR: numeric field overflow DETAIL: A field with precision 6, scale 4 must round to an absolute value less than 10^2. hrdb=#
1.3 序列类型
SMALLSERIAL，SERIAL和BIGSERIAL类型不是真正的数据类型，只是为在表中设置唯一标识做的概念上的便利。因此，创建一个整数字段，并且把它的缺省数值安排为从一个序列发生器读取。应用了一个NOT NULL约束以确保NULL不会被插入。在大多数情况下用户可能还希望附加一个UNIQUE或PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动的。最后，将序列发生器从属于那个字段，这样当该字段或表被删除的时候也一并删除它。
| 名称 | 描述 | 存储空间 | 范围 |
|---|---|---|---|
| SMALLSERIAL | 二字节序列整型 | 2字节 | 1 - 32,767 |
| SERIAL | 四字节序列整型 | 4字节 | 1 - 2,147,483,647 |
| BIGSERIAL | 八字节序列整型 | 8字节 | 1 - 9,223,372,036,854,775,807 |
示例：
hrdb=# --创建序列类型表 hrdb=# CREATE TABLE tab_serial(col1 smallserial,col2 serial,col3 bigserial); CREATE TABLE hrdb=# --字段注释 hrdb=# COMMENT ON COLUMN tab_serial.col1 IS '小整型序列，从1开始，最大值为32767'; COMMENT hrdb=# COMMENT ON COLUMN tab_serial.col2 IS '小整型序列，从1开始，最大值为2147483647'; COMMENT hrdb=# COMMENT ON COLUMN tab_serial.col3 IS '小整型序列，从1开始，最大值为9223372036854775807'; COMMENT hrdb=# --查看表定义 hrdb=# \d+ tab_serial Table "public.tab_serial" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------+-----------+----------+------------------------------------------+---------+--------------+-------------------------------------------------- col1 | smallint | | not null | nextval('tab_serial_col1_seq'::regclass) | plain | | 小整型序列，从1开始，最大值为32767 col2 | integer | | not null | nextval('tab_serial_col2_seq'::regclass) | plain | | 小整型序列，从1开始，最大值为2147483647 col3 | bigint | | not null | nextval('tab_serial_col3_seq'::regclass) | plain | | 小整型序列，从1开始，最大值为9223372036854775807 hrdb=# --插入数据 hrdb=# INSERT INTO tab_serial VALUES(1,1,1); INSERT 0 1 hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775807); INSERT 0 1 hrdb=# --如果插入的值大于序列整型值的范围，将会整型类型越界的ERROR hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775808); ERROR: bigint out of range hrdb=# INSERT INTO tab_serial VALUES(32767,2147483648,9223372036854775807); ERROR: integer out of range hrdb=# INSERT INTO tab_serial VALUES(32768,2147483647,9223372036854775807); ERROR: smallint out of range hrdb=# --当然，既然是序列类型，那可以插入默认值 hrdb=# INSERT INTO tab_serial hrdb-# VALUES(default,default,default); INSERT 0 1 通过上述示例，可以知道 smallserial,serial,bigserial相当于先创建一个序列，然后在创建表分别指定不同的整型数据类型smallint,integer,bigint。如下示例：
hrdb=# --先创建序列 hrdb=# CREATE SEQUENCE IF NOT EXISTS serial_small hrdb-# INCREMENT BY 1 hrdb-# START WITH 1 hrdb-# NO CYCLE; CREATE SEQUENCE hrdb=# --再创建表 hrdb=# CREATE TABLE IF NOT EXISTS tab_test_serial( hrdb(# col1 smallint default nextval('serial_small'), hrdb(# col2 integer default nextval('serial_small'), hrdb(# col3 bigint default nextval('serial_small') hrdb(# ); CREATE TABLE hrdb=# --插入数据 hrdb=# INSERT INTO tab_tes
相关内容
- 浅谈postgresql数据库varchar、char、text的比较_PostgreSQL_
- postgresql 修改列类型操作_PostgreSQL_
- postgresql查询锁表以及解除锁表操作_PostgreSQL_
- postgresql数据库根据年月查询出本月的所有数据操作_PostgreSQL_
- Postgresql 实现查询一个表/所有表的所有列名_PostgreSQL_
- PostgreSQL 查找当前数据库的所有表操作_PostgreSQL_
- PostgreSQL COALESCE使用方法代码解析_PostgreSQL_
- PgSQl临时表创建及应用实例解析_PostgreSQL_
- PostgreSQL基础知识之SQL操作符实践指南_PostgreSQL_
- Mac系统重置PostgreSQL密码的方法示例代码_PostgreSQL_
点击排行
本栏推荐
