PostgreSQL

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

簡介

PostgreSQL及其他關係型資料庫管理系統使用資料庫和表來構造和組織資料。我們可以快速回顧這兩個術語的定義

資料庫：彼此分離不同結構和資料集
表：定義資料結構並在資料庫中儲存實際資料值

在PostgreSQL中，資料庫和表之間還有一箇中間物件，稱為schema（模式）

schema（模式）：資料庫中的一個名稱空間，包含表、索引、檢視和其他專案。

本指南不會直接涉及PostgreSQL的schema概念，但瞭解它的存在是好的。

相反，我們將重點介紹如何建立和刪除PostgreSQL資料庫和表。示例主要使用SQL，但在結尾處，我們將向您展示如何使用命令列完成其中一些任務。這些替代方法使用標準PostgreSQL安裝中包含的工具，如果您具有PostgreSQL主機的管理訪問許可權，則這些工具可用。

本指南中涵蓋的一些語句，特別是PostgreSQL的CREATE TABLE語句，有許多額外選項，超出了本文的範圍。如果您想了解更多資訊，請查閱官方PostgreSQL文件。

先決條件

要遵循本指南，您需要使用psql命令列客戶端以具有管理許可權的使用者身份登入到PostgreSQL例項。您的PostgreSQL例項可以本地安裝、遠端安裝或由提供商配置。

具體來說，您的PostgreSQL使用者需要CREATE DB許可權或是一個Superuser（超級使用者），您可以在psql中使用\du元命令進行檢查。

\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元命令檢視預設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元命令獲取此資訊

\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。
Name: 某個特定學校物品的名稱。
Description: 物品的簡短描述。
Manufacturer: 物品製造商的名稱。
Color: 物品的顏色。
Inventory: 某種學校用品的庫存數量。此數量不應小於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列約束是一種特殊約束，用於指示可以唯一標識表中記錄的列。因此，該約束規定該列不能為空且必須唯一。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表。此表中應包含以下列

Employee ID（員工ID）: 唯一的員工識別號。
First name（名）: 教師的名。
Last name（姓）: 教師的姓。
Subject（科目）: 教師受聘教授的科目。
Grade level（年級）: 教師受聘教授的學生年級。

使用以下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元命令列出所有表，如我們上面演示的那樣

\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進行操作，您可以在此處結束或跳到結論。如果您想了解如何從命令列建立和刪除資料庫，請繼續閱讀下一節。

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

如果您對PostgreSQL安裝的伺服器或叢集具有shell訪問許可權，您可能可以使用一些額外的命令列工具來幫助建立和刪除資料庫。createdb和dropdb命令隨PostgreSQL安裝時捆綁在一起。

從命令列建立新資料庫

createdb命令的基本語法（應由具有PostgreSQL管理訪問許可權的系統使用者執行）是

createdb db_name

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

該命令也接受選項以改變其行為，就像您之前看到的SQL變體一樣。您可以透過man createdb瞭解更多關於這些選項的資訊。其中一些最重要的選項是

--encoding= : 設定資料庫的字元編碼。
--locale= : 設定資料庫的區域設定。

這些可以幫助確保資料庫能夠以您計劃支援的格式和您專案的本地化偏好儲存資料。

例如，為了確保您的資料庫以Unicode支援建立，並覆蓋伺服器自身的區域設定以使用美式英語本地化，您可以鍵入

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

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

要遵循本指南中的示例，您可以透過鍵入以下命令建立一個名為school的資料庫，使用預設區域設定和UTF8字元編碼

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文件瞭解更多資訊。

常見問題

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中從轉儲檔案建立資料庫？

要從轉儲檔案（pg_dump）建立資料庫，PostgreSQL提供了實用程式pg_restore。

該程式將資料庫恢復到轉儲時的狀態。示例語法如下所示

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

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

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

createdb db_name

如何在PostgreSQL中刪除資料庫？

DROP DATABASE語句告訴PostgreSQL刪除指定的資料庫。基本語法如下所示

DROP DATABASE database_name;

如何在PostgreSQL中更改列的資料型別？

要更改特定列的資料型別，請使用ALTER COLUMN更改命令和SET DATA TYPE列命令。

基本語法包括列名、新型別，以及一個可選的USING子句，用於指定舊型別的轉換方式。

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

關於作者

賈斯汀·埃林伍德

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

如何在RDS上配置PostgreSQL資料庫

PostgreSQL資料型別簡介

在 GitHub 上編輯此頁面

PostgreSQL

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

目錄

簡介

先決條件

建立一個新資料庫

列出現有資料庫

在資料庫中建立表

PostgreSQL `CREATE TABLE` 語法

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

如何在PostgreSQL中建立表

如何建立帶主鍵和外部索引鍵的表

如何在PostgreSQL中查看錶

修改表

修改表語法

向表中新增列

從表中刪除列

更改列的資料型別

其他表更改

刪除表

刪除資料庫

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

從命令列建立新資料庫

從命令列刪除資料庫

結論

常見問題

賈斯汀·埃林伍德

Prisma的資料指南

PostgreSQL

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

目錄

簡介

先決條件

建立一個新資料庫

列出現有資料庫

在資料庫中建立表

PostgreSQL CREATE TABLE 語法

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

如何在PostgreSQL中建立表

如何建立帶主鍵和外部索引鍵的表

如何在PostgreSQL中查看錶

修改表

修改表語法

向表中新增列

從表中刪除列

更改列的資料型別

其他表更改

刪除表

刪除資料庫

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

從命令列建立新資料庫

從命令列刪除資料庫

結論

常見問題

賈斯汀·埃林伍德

Prisma的資料指南

PostgreSQL `CREATE TABLE` 語法

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