CREATE TABLE
PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。
-
普通建表
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( 一个或多个列 ), -- 或 CONSTRAINT idx_name primary key ( 一个或多个列 ) CONSTRAINT idx_name UNIQUE(col_name), CONSTRAINT constraint_name CHECK(expression) )
-
LIKE
CREATE TABLE table_name (LIKE table_name);
如果想完全复制源表列上的约束和其他信息，则： -
INCLUDING DEFAULTS
-
INCLUDING CONSTRAINTS
-
INCLUDING INDEXES
-
INCLUDING STORAGE
-
INCLUDING COMMENTS
-
INCLUDING ALL
ALTER TABLE
-
添加列
ALTER TABLE table_name ADD column_name datatype;
-
删除列
ALTER TABLE table_name DROP COLUMN column_name;
-
修改列类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
-
添加 NOT NULL 约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
-
添加 UNIQUE 约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
-
添加 CHECK 约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
-
添加主键约束
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
-
删除约束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;
-
删除主键
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;
-
设置 TOAST 存储
ALTER TABLE table_name ALTER col_name SET STORAGE EXTERNAL
删除 TABLE
-
DROP TABLE
DROP TABLE table_name;
-
TRUNCATE TABLE
TRUNCATE TABLE table_name
实例
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
接下来我们再创建一个表格：
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
我们可以使用 **\d** 命令来查看表格是否创建成功：
testdb=# \d List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | company | table | postgres public | department | table | postgres (2 rows)
**\d tablename** 查看表格信息：
testdb=# \d company Table "public.company" Column | Type | Collation | Nullable | Default ---------+---------------+-----------+----------+--------- id | integer | | not null | name | text | | not null | age | integer | | not null | address | character(50) | | | salary | real | | | Indexes: "company_pkey" PRIMARY KEY, btree (id)
内部结构
表中的一个页包含如下描述的三种数据：
-
header data：由结构 PageHeaderData 定义的头数据被分配在页面的开头。它是 24 字节长，包含关于页面的一般信息。结构的主要变量如下所述。
-
pd_lsn：lsn 称为 PageLSN，它确定和记录了最后一次修改时写入的 XLOG 记录的 LSN。它是一个 8 字节的无符号整数，与WAL(提前写日志)机制相关。用于恢复数据时校验日志文件和数据文件的一致性；pd_lsn 的高位为 xlogid，低位记录偏移量；因为历史原因，64 位的 LSN 保存为两个 32 位的值。
-
pd_checksum：此变量存储该页的校验和值。(注意，9.3 或更高版本支持此变量；在早期版本中，该部分存储了页面的 timelineId。)
-
pg_flags：标识页面的数据存储情况
-
pd_lower, pd_upper：pd_lower 指向空闲空间的起始位置，而 pd_upper 指向空闲空间的结束位置。
-
pd_special：此变量用于索引。在表内的页中，它指向页的末尾。(在索引内的页面中，它指向索引相关数据的开始位置，根据索引类型(如B-tree、GiST、GiN等)包含特定的数据。)
-
pd_pagesize_version：不同的 PostgreSQL 版本的页的格式可能会不同。
-
line pointer(s)：一个行指针是4字节长，它包含一个指向每个堆元组的指针。它也被称为 item pointer。
-
pd_linp[1]：行指针属组
它扮演元组的索引角色。每个索引从1开始按顺序编号，称为 offset number 。当一个新的元组被添加到页面时，一个新的行指针也被推送到数组中以指向新的元组。
要在表中标识元组，在内部使用**元组标识符(TID)**。TID由一对值组成：包含元组的页面的 block number 和指向元组的行指针的 offset number。它使用的一个典型例子是索引。 -
free space / hole：行结束指针和最新元组开始之间的空白空间。
行尾指针和最新元组开始之间的空白称为 or。 -
heap tuple(s)：堆元组就是记录数据本身。它们是从页面底部开始按顺序堆放的。
虽然 HeapTupleHeaderData 结构包含 7 个字段，但在后续部分中需要 4 个字段。 -
t_xmin：保存插入此元组的事务的 txid。
-
t_xmax：保存删除或更新此元组的事务的 txid。如果这个元组没有被删除或更新，t_xmax 被设置为 *0*，这意味着无效。
-
cmin：创建命令 id
-
cmax：破坏命令id
-
t_cid：保存命令 id (cid)，这意味着从 0 开始在当前事务中执行此命令之前执行了多少条 SQL 命令。例如，假设我们在一个事务中执行三个插入命令：'BEGIN; INSERT; INSERT; INSERT; COMMIT;'。如果第一个命令插入这个元组，t_cid 被设置为 0。如果第二个命令插入它，t_cid 被设置为 1，依此类推。
-
t_ctid：保存指向自身或新元组的元组标识符(tid)。用于标识表中的元组。当这个元组被更新时，这个元组的 t_ctid 指向新的元组；否则，t_ctid 指向它自己。
-
natts：数量的属性
-
t_infomask：元组标记
-
hoff：元组头的长度
-
bits：表示 null 的位映射
使用 pageinspect 插件，查看表内部记录：
TEST=# select * from heap_page_items(get_raw_page('ITEMS', 0)) limit 10;
LP | LP_OFF | LP_FLAGS | LP_LEN | T_XMIN | T_XMAX | T_FIELD3 | T_CTID | T_INFOMASK2 | T_INFOMASK | T_HOFF | T_BITS | T_OID | T_DATA
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------------------------
1 | 8144 | 1 | 48 | 2177 | 0 | 0 | (0,1) | 3 | 2306 | 24 | | | \x010000001732323037382e30333036003375adb6723f0200
2 | 8096 | 1 | 48 | 2177 | 0 | 0 | (0,2) | 3 | 2306 | 24 | | | \x020000001734363034322e31323331003375adb6723f0200
3 | 8048 | 1 | 48 | 2177 | 0 | 0 | (0,3) | 3 | 2306 | 24 | | | \x030000001734373834302e38333135003375adb6723f0200
4 | 8000 | 1 | 48 | 2177 | 0 | 0 | (0,4) | 3 | 2306 | 24 | | | \x040000001733353935312e33313137003375adb6723f0200
5 | 7952 | 1 | 48 | 2177 | 0 | 0 | (0,5) | 3 | 2306 | 24 | | | \x050000001736393235322e35363536003375adb6723f0200
6 | 7904 | 1 | 48 | 2177 | 0 | 0 | (0,6) | 3 | 2306 | 24 | | | \x060000001738333935342e37323239003375adb6723f0200
7 | 7856 | 1 | 48 | 2177 | 0 | 0 | (0,7) | 3 | 2306 | 24 | | | \x070000001731343832382e38343435003375adb6723f0200
8 | 7808 | 1 | 48 | 2177 | 0 | 0 | (0,8) | 3 | 2306 | 24 | | | \x080000001734323237382e37363233003375adb6723f0200
9 | 7760 | 1 | 48 | 2177 | 0 | 0 | (0,9) | 3 | 2306 | 24 | | | \x090000001736323338382e32383638003375adb6723f0200
10 | 7712 | 1 | 48 | 2177 | 0 | 0 | (0,10) | 3 | 2306 | 24 | | | \x0a00000015373432392e3436393400003375adb6723f0200
(10 rows)
查看索引：
TEST=# select * from bt_page_items('ITEMS_PKEY', 1) limit 10;
ITEMOFFSET | CTID | ITEMLEN | NULLS | VARS | DATA
------------+--------+---------+-------+------+-------------------------
1 | (2,53) | 16 | f | f | 6f 01 00 00 00 00 00 00
2 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00
3 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00
4 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
5 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00
6 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00
7 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00
8 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00
9 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00
10 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00
(10 rows)
用 linux 工具查看二进制：
TEST=# select pg_relation_filepath('ITEMS');
SYS_RELATION_FILEPATH
-----------------------
base/14804/16826
(1 row)
TEST=#
[1]+ Stopped ksql -U SYSTEM -d TEST
[postgres@localhost ~]$ cd $PG_DATA
[postgres@localhost data]$ cd base/14804/16826
-bash: cd: base/14804/16826: Not a directory
[postgres@localhost data]$ hexdump -C base/14804/16826|head -n 10
00000000 00 00 00 00 00 74 99 0a 00 00 00 00 40 00 70 1e |.....t......@.p.|
00000010 00 20 04 20 00 00 00 00 d8 9f 4a 00 b0 9f 48 00 |. . ......J...H.|
00000020 88 9f 4a 00 60 9f 48 00 38 9f 4a 00 10 9f 4a 00 |..J.`.H.8.J...J.|
00000030 e8 9e 4a 00 c0 9e 4a 00 98 9e 4a 00 70 9e 4a 00 |..J...J...J.p.J.|
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001e70 85 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001e80 0a 00 02 00 02 08 18 00 0a 00 00 00 13 74 65 73 |.............tes|
00001e90 74 37 30 36 30 00 00 00 85 08 00 00 00 00 00 00 |t7060...........|
00001ea0 00 00 00 00 00 00 00 00 09 00 02 00 02 08 18 00 |................|