PostgreSQL的实践一：数据类型（一）

高璐发布于2019-07-25 12:38 / 2191人阅读

摘要：数据类型类型转换数值类型数值类型序列类型不同于的自增长，和都是序列的方式创建使用创建序列关联列表架构模式名称类型拥有者数据表数据表序列数行记录数据

数据类型

类型转换

select bit"10101010";
select int4"123";
select CAST("123" as int4);
select numeric"123.2352342";
select "123"::int4;
select true::BOOLEAN; -- t
select ("f" is true)::BOOLEAN; -- f

数值类型

数值类型 - 序列类型

不同于mysql的自增长，Postgres和Oracle都是序列的方式创建

# 使用SERIAL创建序列
testdb=# create table test2 (id SERIAL, title varchar(20));
CREATE TABLE
testdb=# 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 SEQUENCE
testdb=# CREATE TABLE test3 (id int4 NOT NULL DEFAULT nextval("test3_id_seq"), name varchar(20));
CREATE TABLE
testdb=# ALTER SEQUENCE test3_id_seq OWNED BY test3.id;
ALTER SEQUENCE
testdb=# 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";
SET
postgres=# 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;  --二进制 AND

select 31|15;  --二进制 OR

select 31#15;  --二进制 XOR

select ~1;  --二进制 NOT

select 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 行记录)

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/39018.html

想熟悉PostgreSQL？这篇就够了

摘要：它在其他开放源代码数据库系统和专有系统之外，为用户又提供了一种选择。将插入空间以填补任何额外的空间。始终被视为唯一值上述两个约束的组合。表范围的约束可以是，，或。如何在中创建表我们将创建一个名为的表，它定义了各种游乐场设备。欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~ 本文由angel_郁发表于云+社区专栏什么是PostgreSQL？ PostgreSQL是自由...

DTeam 2019-07-25 12:36 评论0 收藏0
PostgreSQL的实践一：数据类型（二）

摘要：的实践一数据类型一数据类型复合类型创建一个复合类型命令格式合成类型栏位类型没有找到任何名称为的关联。数据库会对输人的数据进行检查，让一些不符合标准的数据不能存放到数据库中，同时还提供了函数对其类型进行安全性检査。 PostgreSQL的实践一：数据类型（一）数据类型复合类型 # 创建一个复合类型person # 命令格式：create type xxx AS (); testdb...

Mr_houzi 2019-07-25 12:38 评论0 收藏0
新书推荐 |《PostgreSQL实战》出版(提供样章下载)

摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 评论0 收藏0
PostgreSQL的实践一：初识

摘要：每个服务由多个进程组成，为首的进程名为。服务使用字节长的内部事务标识符，即时发生重叠后仍然继续使用，这会导致问题，所以需要定期进行操作。操作被认为是紧跟操作后的操作。在涉及高比例插入删除的表中，会造成索引膨胀，这时候可以重建索引。简介和认知发音 post-gres-q-l 服务（server）一个操作系统中可以启动多个postgres服务。每个服务由多个进程组成，为首的进程名为p...

yibinnn 2019-07-25 12:38 评论0 收藏0