当前位置：首页 > news >正文

PostgreSQL 的数据定义

news 来源：原创 2024/4/27 12:24:42

1、PostgreSQL 支持以下类型的数据：

 布尔
 字符
 数字
 时间（基于时钟）
 PostgreSQL 扩展类型
 二进制大对象（BLOB）

PostgreSQL 字符类型：

定义 意义
char        单个字符
char(n)     一组长度固定为 n 的字符，长度不足用空白填充。如果你尝试存储一个过长的字符串，将会发生一                
            个错误。


varchar(n)   一组长度不超过 n 的字符，长度不足也不需要填充。PostgreSQL 扩展了 SQL标准，允许指定没 
              有长度的 varchar，这实际上使长度不受限制。


text         实际上是一个长度不受限制的字符串，就像 varchar 一样，只是不需要定义最大长度。这是一个 
             PostgreSQL 针对 SQL 标准做的扩展。

按惯例，char(n)在字符串的长度为固定或者行与行之间有稍微变化的时候使用，varchar(n)在字符串长度明显变化的时候使用。这是因为在一些数据库中，内部存储定长字符串的性能比变长的高很多，即使定长的需要存储一些不必要的字符。但是在内部，PostgreSQL 使用相同的机制存储 char 和 varchar 类型。所以，对于 PostgreSQL，使用哪种类型更多依赖于你自己的个人偏好。如果不同行之间的数据的长度明显不同，可以选择 varchar(n)类型。还有，如果你不确定长度，可以使用 varchar(n)。

PostgreSQL 的时间数据类型

定义          意义
date         存储日期信息
time         存储时间信息
timestamp    存储日期和时间
interval     存储 timestamp 之间差别的信息
timestamptz  PostgreSQL 扩展的类型，存储包含时区信息的 timestamp

PostgreSQL 样式的数组

要将一个表的列定义为数组，你可以简单地在类型后面添加[]；不需要定义元素的个数。如果你使用了大小来定义，
PostgreSQL 接受你的定义，但它不强制接受指定数量的元素。

例如：

test=> CREATE TABLE empworkday (
test(> refcode char(5),
test(> workdays int[]
test(> );
CREATE TABLE
test=>
test=> INSERT INTO empworkday VALUES('val01', '{0,1,0,1,1,1,1}');
INSERT 17892 1
test=> INSERT INTO empworkday VALUES('val02', '{0,1,1,1,1,0,1}');
INSERT 17893 1
test=>

可以一次性选择数组元素的所有值

test=> SELECT * FROM empworkday;
refcode | workdays
---------+-----------------
val01 | {0,1,0,1,1,1,1}
val02 | {0,1,1,1,1,0,1}
(2 rows)
test=>

也可以通过给出数组的索引值来取出单个元素：

test=> SELECT workdays[2] FROM empworkday WHERE refcode = 'val02';
workdays
----------
 1
(1 row)

PostgreSQL 的行为很像传统的编程语言，存储一个数组的值，甚至还有不需要指出数组的大小的好处。如果你选择整个数组，PostgreSQL 显示在花括号之间的所有的用逗号分隔的值。有一个需要注意的事情是 PostgreSQL 的数组中第一个元素的索引值是 1 而不是 0，而很多编程语言通常是 0.如果你尝试选择一个不存在的数组元素，将返回 NULL。

2、数据操作

在数据类型之间转换

PostgreSQL 使用 cast 转换符，以下两种方式：

cast(column-name AS type-definition-to-convert-to)


column-name::type-definition-to-convert-to

例如：

bpsimple=> SELECT cast(date_placed AS char(10)) FROM orderinfo;
date_placed
-------------
2004-03-13
2004-06-23
2004-09-02
2004-09-03
2004-07-21
(5 rows)
bpsimple=>
bpsimple=> SELECT sell_price, sell_price::int AS "Guide Price" FROM item
WHERE sell_price > 5.0;
sell_price | Guide Price
------------+-------------
 21.95 | 22
 9.95 | 10
 15.75 | 16
 19.95 | 20
 25.32 | 25
 11.49 | 11
(6 rows)
bpsimple=>

PostgreSQL的数据操作函数

函数                                             描述
length(column-name)                         返回一个字符串的长度
trim(column-name)                           移除字符串开始和结尾的空格
strpos(column-name, string)                 返回子串在列中的位置
substr(column-name, position, length)       根据指定位置和长度截取子串。第一个字符算作位置 1
round(column-name, length)                 根据指定小数点位置四舍五入一个数字
abs(number)                                 获得一个数字的绝对值

3、PostgreSQL 的表管理

以下为建表的基本语法：

CREATE [TEMPORARY] TABLE table-name (
 { column-name type [ column-constraint ] [,...] }
 [ CONSTRAINT table-constraint ]
) [ INHERITS (existing-table-name) ]

PostgreSQL 的扩展，INHERITS，它允许一个建立一个新表，继承已经存在的表的列。新表除了包含指定
的字段外，还包含在 INHERITS 关键字之后的表的所有字段。

PostgreSQL 中最主要的列约束

定义                                                         意义
NOT NULL                                          列不允许存储 NULL 值

UNIQUE                                            列中存储的的值必须与其他行都不同。PostgreSQL                                         
                                              允许你在定义为 UNIQUE 列上存储任意多个 NULL 值。

PRIMARY KEY                                       实际上是一个 NOT NULL 和 UNIQUE 的组合。每                
                                           个表只能有一个列被标记为 PRIMARY KEY（但你可以有多个        
                                          列被同时标记为 NOT NULL 和 UNIQUE）。如果你需要建立 
                                         一个组合的主键（一个包含超过一个列的主键），你必须使用 
                                          一个表级的约束，而不是列级的约束。



DEFAULT default-value                       允许你在插入数据的时候提供一个默认值。（严格来说，这                
                                         不是一个约束选项，但把它作为约束来考虑更容易理解。）


CHECK (condition)                           当插入或者更新数据的时候允许你进行一个条件检查。


REFERENCES                                 约束这个值必须为另一个独立的表的某个列中的某个值。

使用：

test=> CREATE TABLE testcolcons (
test(> colnotnull INT NOT NULL,
test(> colunique INT UNIQUE,
test(> colprikey INT PRIMARY KEY,
test(> coldefault INT DEFAULT 42,
test(> colcheck INT CHECK( colcheck < 42)
test(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testcolcons_pkey" for table "testcolcons"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"testcolcons_colunique_key" for table "testcolcons"
CREATE TABLE
test=>

使用临时表 ;

当你建表时，你可以使用 CREATE TEMPORARY TABLE（你也可以使用同义词 CREATE TEMP TABLE）而不是 CREATE TABLE。这个表使用通常的方法为你创建，除了当你的会话结束，你到数据库的连接断开后，临时表会自动删除

4、视图

建立视图的语法非常简单：

CREATE VIEW name-of-view AS select-statement;

例如：

CREATE VIEW item_price AS SELECT item_id, description, sell_price FROM item;

5、外键约束

作为一个列的约束的外键，例如：要在 orderinfo 表的 customer_id 列中定义一个外键约束，关联它到 customer 表，我们一桶使用 REFERENCES 关
键字和外部表名和列，就像这样：

CREATE TABLE orderinfo
(
 orderinfo_id serial ,
 customer_id integer NOT NULL REFERENCES customer(customer_id),
 date_placed date NOT NULL,
 date_shipped date ,
 shipping numeric(7,2) ,
 CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id)
);

作为一个表的约束的外键，例如：我们可以更新我们 orderinfo 表的定义来声明一个列 customer_id 为一个外键的约束，因为它关联到 customer 表的主键列 customer_id：

CREATE TABLE orderinfo
(
 orderinfo_id serial ,
 customer_id integer NOT NULL,
 date_placed date NOT NULL,
 date_shipped date ,
 shipping numeric(7,2) ,
 CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id),
 CONSTRAINT orderinfo_customer_id_fk FOREIGN KEY(customer_id) REFERENCES
 customer(customer_id)
);

ALTER TABLE 为已有的表添加外键约束，例如：

bpsimple=> ALTER TABLE orderinfo ADD CONSTRAINT
 orderinfo_customer_id_fk FOREIGN KEY(customer_id)
 REFERENCES customer(customer_id);
ALTER TABLE
bpsimple=>