PostgreSQL

如何在 PostgreSQL 中建立及刪除資料庫和表格

簡介

PostgreSQL 和其他關聯式資料庫管理系統使用資料庫和表格來組織和結構化其資料。我們可以快速複習這兩個術語的定義

資料庫： 將不同的結構和資料集彼此分隔開來
表格： 定義資料結構並在資料庫中儲存實際的資料值

在 PostgreSQL 中，資料庫和表格之間還有一個中間物件，稱為結構描述

結構描述： 資料庫中的命名空間，包含表格、索引、檢視表和其他項目。

本指南不會直接探討 PostgreSQL 的結構描述概念，但了解它的存在是件好事。

相反地，我們將專注於如何建立和刪除 PostgreSQL 資料庫和表格。範例主要會使用 SQL，但接近尾聲時，我們將向您展示如何使用命令列執行其中幾項任務。這些替代方案使用標準 PostgreSQL 安裝中包含的工具，如果您擁有 PostgreSQL 主機的管理權限，即可使用這些工具。

本指南涵蓋的某些陳述式，特別是 PostgreSQL CREATE TABLE 陳述式，有許多超出本文範圍的額外選項。如果您想要更多資訊，請查看官方 PostgreSQL 文件以了解更多資訊。

先決條件

為了遵循本指南，您需要使用psql 命令列用戶端，以具有管理權限的使用者身分登入 PostgreSQL 執行個體。您的 PostgreSQL 執行個體可以在本機、遠端安裝，或由供應商佈建。

具體而言，您的 PostgreSQL 使用者需要具有 CREATE DB 權限或成為 Superuser，您可以使用 psql 中的 \du meta-command 來檢查

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres 超級使用者在安裝時會自動建立，具有所需的權限，但您可以使用任何具有 Create DB 權限的使用者。

建立新的資料庫

一旦您使用 psql 或任何其他 SQL 用戶端連線到 PostgreSQL 執行個體，您就可以使用 SQL 建立資料庫。

建立資料庫的基本語法是

CREATE DATABASE db_name;

這會在目前的伺服器上建立一個名為 db_name 的資料庫，並將目前使用者設定為新資料庫的擁有者，使用預設資料庫設定。您可以使用以下 psql meta-command 來檢視預設 template1 範本的屬性

\l template1

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(1 row)

您可以新增其他參數來變更資料庫的建立方式。以下是一些常見的選項

ENCODING： 設定資料庫的字元編碼。
LC_COLLATE： 設定資料庫的定序或排序順序。這是一個本地化選項，決定了項目在排序時的組織方式。
LC_CTYPE： 設定新資料庫的字元分類。這是一個本地化選項，會影響哪些字元被視為大寫、小寫和數字。

這些可以協助確保資料庫能夠以您計畫支援的格式以及專案的本地化偏好設定來儲存資料。

例如，為了確保您的資料庫是以 Unicode 支援建立的，並覆寫伺服器本身的地區設定以使用美式英文本地化（這些設定恰好都與上面顯示的 template1 中的值相符，因此實際上不會發生任何變更），您可以輸入

CREATE DATABASE db_name
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

為了遵循本指南中的範例，請使用您執行個體的預設地區設定和 UTF8 字元編碼建立一個名為 school 的資料庫

CREATE DATABASE school ENCODING 'UTF8';

這將使用您提供的規格建立新的資料庫。

列出現有的資料庫

若要判斷伺服器或叢集上目前有哪些可用的資料庫，您可以使用以下 SQL 陳述式

SELECT datname FROM pg_database;

這將列出環境中目前定義的每個資料庫

  datname
-----------
 _dodb
 template1
 template0
 defaultdb
 school
(5 rows)

如前所述，如果您使用 psql 用戶端連線，您也可以使用 \l meta-command 取得此資訊

\l

這將顯示可用的資料庫名稱，以及其擁有者、編碼、地區設定和權限

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

我們建立的 school 資料庫會顯示在系統上的其他資料庫之中。這是概觀伺服器或叢集中資料庫的好方法。

在資料庫中建立表格

在建立一個或多個資料庫之後，您可以開始定義表格來儲存您的資料。表格由名稱和已定義的結構描述組成，結構描述決定了每個記錄必須包含的欄位和資料類型。

PostgreSQL `CREATE TABLE` 語法

您可以使用 CREATE TABLE 陳述式建立表格。此命令的簡化基本語法如下所示

CREATE TABLE table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

上述語法的組成部分包括以下內容

CREATE TABLE table_name：基本建立陳述式，表示您希望定義表格。table_name 預留位置應替換為您想要使用的表格名稱。
column_name TYPE：定義表格中的基本欄位。column_name 預留位置應替換為您想要用於欄位的名稱。TYPE 指定欄位的PostgreSQL 資料類型。儲存在表格中的資料必須符合欄位結構和欄位資料類型才能被接受。
column_constraint：欄位約束是可選的限制，用於進一步限制可以儲存在欄位中的資料。例如，您可以要求條目不得為 null、唯一或正整數。
table_constraints：表格約束與欄位約束類似，但涉及多個欄位的互動。例如，您可以有一個表格約束，檢查表格中的 DATE_OF_BIRTH 是否在 DATE_OF_DEATH 之前。

使用 `IF NOT EXISTS` 子句有條件地建立表格

預設情況下，如果您嘗試在 PostgreSQL 中建立一個已存在於資料庫中的表格，則會發生錯誤。為了在您想要建立表格（如果表格不存在），但如果表格已存在則繼續的情況下解決此問題，您可以使用 IF NOT EXISTS 子句。IF NOT EXISTS 可選限定詞會告知 PostgreSQL，如果資料庫已存在，則忽略該陳述式。

若要使用 IF NOT EXISTS 子句，請將其插入到 CREATE TABLE 語法之後和表格名稱之前的命令中

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

此變體將嘗試建立表格。如果具有該名稱的表格已存在於指定的資料庫中，PostgreSQL 將拋出警告，指示表格名稱已被佔用，而不是失敗並出現錯誤。

如何在 PostgreSQL 中建立表格

上述語法足以建立基本表格。作為範例，我們將在 school 資料庫中建立兩個表格。一個表格將稱為 supplies，另一個將稱為 teachers

在 supplies 表格中，我們希望具有以下欄位

ID： 每種類型學校用品的唯一 ID。
名稱： 特定學校用品的名稱。
描述： 該項目的簡短描述。
製造商： 項目製造商的名稱。
顏色： 該項目的顏色。
庫存： 我們擁有的某種類型學校用品的數量。這絕不應小於 0。

我們可以使用以下 SQL 建立具有上述特性的 supplies 表格。

首先，使用 psql 切換到您建立的 school 資料庫，方法是輸入

\c school

這將變更我們未來命令的目標資料庫。您的提示符號應變更以反映資料庫。

接下來，使用以下陳述式建立 supplies 表格

CREATE TABLE supplies (
  id INT PRIMARY KEY,
  name VARCHAR,
  description VARCHAR,
  manufacturer VARCHAR,
  color VARCHAR,
  inventory int CHECK (inventory > 0)
);

這將在 school 資料庫中建立 supplies 表格。PRIMARY KEY 欄位約束是一種特殊約束，用於指示可以唯一識別表格中記錄的欄位。因此，此約束指定欄位不得為 null 且必須是唯一的。PostgreSQL 會為主鍵欄位建立索引，以提高查詢速度。

輸入以下內容以驗證新表格是否存在

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
(1 row)

輸入以下內容以驗證結構描述是否反映預期的設計

\d supplies

                      Table "public.supplies"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 id           | integer           |           | not null |
 name         | character varying |           |          |
 description  | character varying |           |          |
 manufacturer | character varying |           |          |
 color        | character varying |           |          |
 inventory    | integer           |           |          |
Indexes:
    "supplies_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "supplies_inventory_check" CHECK (inventory > 0)

我們可以看見我們指定的每個欄位和資料類型。我們為 inventory 欄位定義的欄位約束列在最後。

接下來，我們將建立 teachers 表格。在此表格中，應存在以下欄位

員工 ID：唯一的員工識別號碼。
名字：教師的名字。
姓氏：教師的姓氏。
科目：教師受聘教授的科目。
年級：教師受聘教授的學生的年級。

使用以下 SQL 建立具有上述結構描述的 teachers 表格

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR,
  grade_level int
);

如何建立具有主鍵和外來鍵的表格

您可以在我們其他 PostgreSQL 指南中找到有關建立具有主鍵和外來鍵的表格的資訊。主鍵和外來鍵都是 PostgreSQL 內資料庫約束的類型。

主鍵是一個特殊欄位或欄位，保證在同一個表格中的各個列中是唯一的。所有主鍵都可以用來唯一識別特定的列。主鍵不僅確保每個列在主鍵欄位中都有唯一值，它們還確保沒有任何列在該欄位中包含 NULL 值。通常，PostgreSQL 中的主鍵使用以下格式來指定自動指派的遞增主鍵：id SERIAL PRIMARY KEY。

外來鍵是一種確保一個表格中的一個或多個欄位與另一個表格中包含的值相符的方法。這有助於確保表格之間的參考完整性。

如何在 PostgreSQL 中檢視表格

在 PostgreSQL 中，您可以透過幾種不同的方式列出表格，具體取決於您要尋找的資訊。

如果您想查看資料庫中有哪些可用的表格，您可以使用 psql 用戶端隨附的 \dt meta-command 來列出所有表格，如我們上面示範的那樣

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
 public | teachers | table | doadmin
(2 rows)

您也可以檢查表格的結構描述是否符合您的規格

\d teachers

                     Table "public.teachers"
   Column    |       Type        | Collation | Nullable | Default
-------------+-------------------+-----------+----------+---------
 id          | integer           |           | not null |
 first_name  | character varying |           |          |
 last_name   | character varying |           |          |
 subject     | character varying |           |          |
 grade_level | integer           |           |          |
Indexes:
    "teachers_pkey" PRIMARY KEY, btree (id)

teachers 表格似乎符合我們的定義。

變更表格

如果您需要變更 PostgreSQL 中現有表格的結構描述，您可以使用 ALTER TABLE 命令。ALTER TABLE 命令與 CREATE TABLE 命令非常相似，但適用於現有表格。

變更表格語法

在 PostgreSQL 中修改表格的基本語法如下所示

ALTER TABLE <table_name> <change_command> <change_parameters>

<change_command> 表示您想要進行的確切變更類型，無論它是否涉及設定表格上的不同選項、新增或移除欄位，或變更類型或約束。<change_parameters> 命令部分包含 PostgreSQL 完成變更所需的任何其他資訊。

將欄位新增至表格

您可以使用 ADD COLUMN 變更命令將欄位新增至 PostgreSQL 表格。變更參數將包括欄位名稱、類型和選項，就像您在 CREATE TABLE 命令中指定它們一樣。

例如，若要將名為 missing_column 且類型為 text 的欄位新增至名為 some_table 的表格，您需要輸入

ALTER TABLE some_table ADD COLUMN missing_column text;

從表格中移除欄位

相反地，如果您想要移除現有的欄位，則可以使用 DROP COLUMN 命令。您需要指定您想要刪除的欄位名稱作為變更參數

ALTER TABLE some_table DROP COLUMN useless_column;

變更欄位的資料類型

若要變更 PostgreSQL 用於特定欄位的資料類型，您可以使用 ALTER COLUMN 變更命令和 SET DATA TYPE 欄位命令。參數包括欄位名稱、其新類型，以及可選的 USING 子句，用於指定應如何將舊類型轉換為新類型。

例如，若要使用明確轉換將 resident 表格中 id 欄位的值設定為 int，我們可以輸入以下內容

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

其他表格變更

可以使用 ALTER TABLE 命令實現許多其他類型的變更。如需有關可用選項的更多資訊，請查看 PostgreSQL 官方文件中關於 ALTER TABLE 的說明。

刪除表格

如果您想要刪除表格，可以使用 DROP TABLE SQL 陳述式。這將刪除表格以及其中儲存的任何資料。

基本語法如下所示

DROP TABLE table_name;

如果表格存在，這將刪除表格，如果表格名稱不存在，則會拋出錯誤。

如果您希望在表格存在時刪除表格，而在表格不存在時不執行任何操作，則可以在陳述式中包含 IF EXISTS 限定詞

DROP TABLE IF EXISTS table_name;

預設情況下，具有對其他表格或物件的依賴關係的表格在這些依賴關係存在時無法刪除。為了避免錯誤，您可以選擇性地包含 CASCADE 參數，這會自動刪除任何依賴關係以及表格

DROP TABLE table_name CASCADE;

如果任何表格具有外來鍵約束，該約束參照您正在刪除的表格，則該約束將自動刪除。

輸入以下內容以刪除我們稍早建立的 supplies 表格

DROP TABLE supplies;

我們將保留 teachers 資料庫，以示範刪除資料庫的陳述式也會移除所有子物件，例如表格。

刪除資料庫

DROP DATABASE 陳述式告知 PostgreSQL 刪除指定的資料庫。基本語法如下所示

DROP DATABASE database_name;

將 database_name 預留位置替換為您想要移除的資料庫名稱。如果找到資料庫，這將刪除資料庫。如果找不到資料庫，則會發生錯誤

DROP DATABASE some_database;

ERROR:  database "some_database" does not exist

如果您希望在資料庫存在時刪除資料庫，否則不執行任何操作，請包含可選的 IF EXISTS 選項

DROP DATABASE IF EXISTS some_database;

NOTICE:  database "some_database" does not exist, skipping
DROP DATABASE

這將移除資料庫，如果找不到資料庫，則不執行任何操作。

若要移除我們在本指南中使用的 school 資料庫，請列出系統上現有的資料庫

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

開啟與您不想要刪除的其中一個資料庫的新連線

\c defaultdb

新的連線開啟後，使用以下命令刪除 school 資料庫

DROP DATABASE school;

這將移除 school 資料庫以及其中定義的 teachers 表格。

如果您一直以來都是使用 SQL，您可以在此結束，或跳至結論。如果您想了解如何從命令列建立和刪除資料庫，請繼續閱讀下一節。

使用管理命令列工具建立和刪除資料庫

如果您可以 shell 存取 PostgreSQL 安裝所在的伺服器或叢集，您可能會存取到一些額外的命令列工具，這些工具可以協助建立和刪除資料庫。createdb 和 dropdb 命令在安裝 PostgreSQL 時會一併捆綁。

從命令列建立新的資料庫

createdb 命令（應由具有 PostgreSQL 管理員權限的系統使用者執行）的基本語法為

createdb db_name

這將使用預設設定在 PostgreSQL 內建立一個名為 db_name 的資料庫。

此命令也接受選項來變更其行為，就像您稍早看到的 SQL 變體一樣。您可以使用 man createdb 找到更多關於這些選項的資訊。一些最重要的選項為

--encoding=：設定資料庫的字元編碼。
--locale=：設定資料庫的locale。

這些可以協助確保資料庫能夠以您計畫支援的格式以及專案的本地化偏好設定來儲存資料。

例如，為了確保您的資料庫在建立時支援 Unicode，並覆寫伺服器本身的 locale 以使用美式英語本地化，您可以輸入

createdb --encoding=UTF8 --locale=en_US db_name

假設您具有正確的權限，資料庫將根據您的規格建立。

為了跟隨本指南中的範例，您可以透過輸入以下內容，使用預設 locale 和 UTF8 字元編碼建立一個名為 school 的資料庫

createdb --encoding=UTF8 school

然後您可以使用 psql 連接到資料庫，以照常設定您的表格。

從命令列刪除資料庫

dropdb 命令與 DROP DATABASE SQL 陳述式相互呼應。它具有以下基本語法

dropdb database_name

變更 database_name 預留位置以參考您想要刪除的資料庫。

預設情況下，如果找不到指定的資料庫，此命令將導致錯誤。為了避免這種情況，您可以包含可選的 --if-exists 標記

dropdb --if-exists database_name

如果指定的資料庫存在，這將刪除它。否則，它將不執行任何操作。

若要刪除我們稍早建立的 school 資料庫，請輸入

dropdb school

這將移除資料庫以及任何子元素，例如其中的表格。

結論

本文涵蓋了如何在 PostgreSQL 中建立和刪除資料庫和表格的基本知識。這些是設定資料庫系統和定義資料結構所需的一些最基本命令。

如先前所述，本 PostgreSQL 教學課程中涵蓋的 SQL 陳述式，尤其是 CREATE TABLE 陳述式，具有許多額外的參數可用於變更 PostgreSQL 的行為。您可以查看官方 PostgreSQL 文件來了解更多相關資訊。

PRISMA.IO 上的相關內容

當使用 Prisma 開發 PostgreSQL 時，您通常會使用 Prisma Migrate 建立資料庫和表格。您可以從我們的使用 Prisma Migrate 開發指南中學習如何使用它。

Prisma 是一個適用於 Typescript 和 Node.js 的開放原始碼資料庫工具組，旨在讓應用程式開發人員在使用資料庫時更有效率和更有信心。

常見問題

PostgreSQL 在使用 `CREATE DATABASE` 命令時是否支援 `IF NOT EXISTS` 子句？

是的，PostgreSQL 支援在建立資料庫和表格時使用 IF NOT EXISTS。以下示範針對表格建立使用此子句。

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

如何在 PostgreSQL 中從 dump 檔案建立資料庫？

若要從 dump 檔案 (pg_dump) 建立資料庫，PostgreSQL 提供了 utility 程式 pg_restore。

此程式會將資料庫重新建立為 dump 時的相同狀態。範例語法如下所示

pg_restore [connection-option...][option...][filename]

哪個命令列會在 PostgreSQL 中建立資料庫？

若要在 PostgreSQL 中建立資料庫，請使用 createdb 命令。語法如下

createdb db_name

如何在 PostgreSQL 中刪除資料庫？

DROP DATABASE 陳述式會告知 PostgreSQL 刪除指定的資料庫。基本語法如下所示

DROP DATABASE database_name;

如何在 PosgreSQL 中變更資料行的資料類型？

若要變更特定資料行的資料類型，請將 ALTER COLUMN 變更命令與 SET DATA TYPE 資料行命令一起使用。

基本語法包含資料行名稱、新類型，以及可選的 USING 子句，用於指定舊類型的轉換。

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

關於作者

Justin Ellingwood

Justin 自 2013 年以來一直撰寫關於資料庫、Linux、基礎架構和開發人員工具的文章。他目前與妻子和兩隻兔子住在柏林。他通常不必以第三人稱寫作，這對所有相關方來說都是一種解脫。

PostgreSQL 資料類型簡介

在 PostgreSQL 中使用日期

在 GitHub 上編輯此頁面

PostgreSQL

如何在 PostgreSQL 中建立及刪除資料庫和表格

目錄

簡介

先決條件

建立新的資料庫

列出現有的資料庫

在資料庫中建立表格

PostgreSQL `CREATE TABLE` 語法

使用 `IF NOT EXISTS` 子句有條件地建立表格

如何在 PostgreSQL 中建立表格

如何建立具有主鍵和外來鍵的表格

如何在 PostgreSQL 中檢視表格

變更表格

變更表格語法

將欄位新增至表格

從表格中移除欄位

變更欄位的資料類型

其他表格變更

刪除表格

刪除資料庫

使用管理命令列工具建立和刪除資料庫

從命令列建立新的資料庫

從命令列刪除資料庫

結論

常見問題

Justin Ellingwood

Prisma 的資料指南

PostgreSQL

如何在 PostgreSQL 中建立及刪除資料庫和表格

目錄

簡介

先決條件

建立新的資料庫

列出現有的資料庫

在資料庫中建立表格

PostgreSQL CREATE TABLE 語法

使用 IF NOT EXISTS 子句有條件地建立表格

如何在 PostgreSQL 中建立表格

如何建立具有主鍵和外來鍵的表格

如何在 PostgreSQL 中檢視表格

變更表格

變更表格語法

將欄位新增至表格

從表格中移除欄位

變更欄位的資料類型

其他表格變更

刪除表格

刪除資料庫

使用管理命令列工具建立和刪除資料庫

從命令列建立新的資料庫

從命令列刪除資料庫

結論

常見問題

Justin Ellingwood

Prisma 的資料指南

PostgreSQL `CREATE TABLE` 語法

使用 `IF NOT EXISTS` 子句有條件地建立表格