PostgreSQL：表

Ryan_Bai發表於2020-12-14
SQL

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)

內部結構

表中的一個頁包含如下描述的三種資料：

  1. 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 |................|

    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31490526/viewspace-2741964/，如需轉載，請註明出處，否則將追究法律責任。

    相關文章