PostgreSQL / 插入和修改資料

如何在 PostgreSQL 中插入和刪除資料

內容

簡介
檢閱表格結構
使用 INSERT 將新紀錄新增至表格
從 INSERT 陳述式傳回資料
使用 INSERT 一次新增多列
使用 DELETE 從表格中移除列
從 DELETE 陳述式傳回資料
使用 DELETE 一次移除多列
結論
常見問題

分享於

簡介

在表格中新增和移除紀錄是資料庫執行的最常見操作之一。新增資料涉及指定您希望新增值的表格和欄位名稱，以及您希望輸入到每個欄位的數值。刪除紀錄涉及識別正確的列或多列，並將它們從表格中移除。

在本指南中，我們將涵蓋如何在 PostgreSQL 中使用 SQL INSERT 和 DELETE 命令。這包括基本語法、如何傳回關於已處理資料的資料資訊，以及如何在單一陳述式中新增或移除多列。

檢閱表格結構

在使用 INSERT 命令之前，您必須了解表格的結構，以便您可以符合表格欄位、資料類型和約束所施加的需求。取決於您的資料庫用戶端，有幾種不同的方法可以做到這一點。

如果您使用的是 psql 命令列用戶端，找到此資訊最直接的方式是使用工具內建的 \d+ meta 命令。

例如，要找到名為 employee 的表格結構，您將輸入此內容

\d+ employee

                                                                Table "public.employee"
   Column    | Type                        | Collation | Nullable | Default                                       | Storage  | Stats target | Description
-------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 employee_id | integer                     |           | not null | nextval('employee_employee_id_seq'::regclass) | plain    |              |
 first_name  | character varying(45)       |           | not null |                                               | extended |              |
 last_name   | character varying(45)       |           | not null |                                               | extended |              |
 last_update | timestamp without time zone |           | not null | now()                                         | plain    |              |
Indexes:
    "employee_pkey" PRIMARY KEY, btree (employee_id)
    "idx_employee_last_name" btree (last_name)
Triggers:
    last_updated BEFORE UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION last_updated()
Access method: heap

輸出會顯示表格的欄位名稱、資料類型和預設值等等。

\d+ meta 命令僅適用於 psql 用戶端，因此如果您使用的是不同的用戶端，您可能必須直接查詢表格資訊。您可以使用如下的查詢取得大多數相關資訊

SELECT column_name, data_type, column_default, is_nullable, character_maximum_length
FROM information_schema.columns WHERE table_name ='employee';

 column_name | data_type                   | column_default                                | is_nullable | character_maximum_length
-------------+-----------------------------+-----------------------------------------------+-------------+--------------------------
 employee_id | integer                     | nextval('employee_employee_id_seq'::regclass) | NO          |
 first_name  | character varying           |                                               | NO          | 45
 last_name   | character varying           |                                               | NO          | 45
 last_update | timestamp without time zone | now()                                         | NO          |
(4 rows)

這些應該能讓您很好地了解表格的結構，以便您可以正確地插入值。

使用 `INSERT` 將新紀錄新增至表格

SQL INSERT 命令用於將資料列新增至現有表格。一旦您知道表格的結構，您就可以建構一個命令，將表格的欄位與您希望為新紀錄插入的對應值相匹配。

命令的基本語法如下所示

INSERT INTO my_table(column1, column2)
VALUES ('value1', 'value2');

欄位清單中的欄位與值清單中提供的值直接對應。

預設情況下，INSERT 命令會傳回物件 ID（通常為 0）和成功插入的列數

INSERT 0 1

例如，若要將新員工插入上面列出的 employee 表格中，我們可以輸入

INSERT INTO employee(first_name, last_name)
VALUES ('Bob', 'Smith');

INSERT 0 1

在這裡，我們為 first_name 和 last_name 欄位提供值，同時讓其他欄位由其預設值填入。如果您查詢表格，您可以看到已新增新紀錄

SELECT * FROM employee;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           1 | Bob        | Smith     | 2020-08-19 21:07:00.952454
(1 row)

PRISMA.IO 相關資訊

您也可以使用 Prisma Client 發出 create query，將資料新增至您的表格。

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

從 `INSERT` 陳述式傳回資料

如果您想要關於新增至表格的資料的額外資訊，您可以在陳述式末尾包含 RETURNING 子句。RETURNING 子句指定要顯示剛插入紀錄的哪些欄位。

例如，若要顯示剛插入紀錄的所有欄位，您可以輸入類似這樣的內容

INSERT INTO my_table(column_name, column_name_2)
VALUES ('value', 'value2')
RETURNING *;

 column_name | column_name_2
-------------+---------------
 value       | value2
(1 row)

INSERT 0 1

使用 employee 表格，看起來會像這樣

INSERT INTO employee(first_name, last_name)
VALUES ('Sue', 'Berns')
RETURNING *;

 employee_id | first_name | last_name |       last_update
-------------+------------+-----------+--------------------------
           2 | Sue        | Berns     | 2020-08-19 21:15:01.7622
(1 row)

INSERT 0 1

您也可以選擇僅傳回插入的特定欄位。例如，在這裡，我們只對新員工的 ID 感興趣

INSERT INTO employee(first_name, last_name)
VALUES ('Delores', 'Muniz')
RETURNING employee_id;

 employee_id
-------------
           3
(1 row)

INSERT 0 1

與往常一樣，您也可以使用欄位別名來變更輸出中的欄位名稱

INSERT INTO employee(first_name, last_name)
VALUES ('Simone', 'Kohler')
RETURNING employee_id AS "Employee ID";

 Employee ID
-------------
           4
(1 row)

INSERT 0 1

使用 `INSERT` 一次新增多列

一次一個陳述式地插入紀錄比一次插入多列更耗時且效率更低。PostgreSQL 允許您指定要新增到同一個表格的多列。每個新列都封裝在括號中，每組括號之間用逗號分隔。

多紀錄插入的基本語法如下所示

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

對於我們一直引用的 employee 表格，您可以透過輸入以下內容在單一陳述式中新增四名新員工

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

INSERT 0 4

使用 `DELETE` 從表格中移除列

SQL DELETE 命令用於從表格中移除列，其功能與 INSERT 互補。為了從表格中移除列，您必須透過在 WHERE 子句中提供比對條件來識別您希望鎖定的列。

基本語法如下所示

DELETE FROM my_table
WHERE <condition>;

例如，對於我們 employee 表格中每個 first_name 設定為 Abigail 的列，我們可以輸入此內容

DELETE FROM employee
WHERE first_name = 'Abigail';

DELETE 1

此處的傳回值表示 DELETE 命令已處理，並移除了一列。

PRISMA.IO 相關資訊

若要使用 Prisma Client 從您的表格中移除資料，請使用 delete query。

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

從 `DELETE` 陳述式傳回資料

與 INSERT 命令一樣，您可以透過新增 RETURNING 子句來傳回受影響的列或已刪除列中的特定欄位

DELETE FROM my_table
WHERE <condition>
RETURNING *;

例如，我們可以透過傳回已刪除 employee 的所有欄位來驗證是否移除了正確的紀錄

DELETE FROM employee
WHERE last_name = 'Smith'
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           1 | Bob        | Smith     | 2020-08-19 21:07:00.952454
(1 row)

DELETE 1

使用 `DELETE` 一次移除多列

您可以透過操作 WHERE 子句中指定的選取條件，使用 DELETE 一次移除多個項目。

例如，若要依 ID 移除多列，您可以輸入類似這樣的內容

DELETE FROM employee
WHERE employee_id in (3,4)
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           3 | Delores    | Muniz     | 2020-08-19 21:17:06.943608
           4 | Simone     | Kohler    | 2020-08-19 21:19:19.298833
(2 rows)

DELETE 2

您甚至可以省略 WHERE 子句以移除給定表格中的所有列

DELETE FROM employee
RETURNING *;

 employee_id | first_name | last_name |        last_update
-------------+------------+-----------+----------------------------
           2 | Sue        | Berns     | 2020-08-19 21:15:01.7622
           6 | Tamal      | Wayne     | 2020-08-19 22:11:53.408531
           7 | Katie      | Singh     | 2020-08-19 22:11:53.408531
           8 | Filipe     | Espinosa  | 2020-08-19 22:11:53.408531
(4 rows)

DELETE 4

但是請注意，使用 DELETE 清空表格資料的效率不如 TRUNCATE 命令，後者可以在不掃描表格的情況下移除資料。

PRISMA.IO 相關資訊

Prisma Client 使用一個名為 deleteMany 的獨立查詢來一次刪除多列資料。

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

結論

在本文中，我們介紹了一些最重要的命令，以控制 PostgreSQL 表格中的資料。INSERT 命令可用於將新資料新增至表格，而 DELETE 命令指定應移除哪些列。這兩個命令都能傳回它們影響的列，並且可以一次對多列進行操作。

這兩個命令是用於管理表格包含的紀錄數量增加或減少的主要機制。掌握它們的基本語法以及它們與其他子句結合的方式，將使您能夠根據需要填入和清理表格。

常見問題

如何在 PostgreSQL 中執行批次插入？

多紀錄插入的基本語法如下所示

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

使用員工資料的範例看起來會像這樣

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

如何在 PostgreSQL 中插入之前檢查紀錄是否存在？

在 PostgreSQL 中插入之前檢查紀錄是否存在的一種方法是使用 EXISTS 子查詢運算式。

EXISTS 條件與您正在檢查的資料的子查詢結合使用。如果子查詢傳回至少一列，則視為符合條件。如果沒有傳回任何列，則表示該紀錄尚不存在。

基本語法如下所示

WHERE EXISTS ( subquery );

如何在 PostgreSQL 中刪除重複列？

在 PostgreSQL 中有幾種刪除重複列的方法。您可以使用 DELETE USING 陳述式來檢查兩個不同的列是否具有相同的值，然後刪除重複項。

此外，您可以使用子查詢來刪除重複項，或使用具有下列步驟的中介表格

建立一個與應移除重複列的表格具有相同結構的新表格。
將來源表格中的相異列插入到中介表格。
刪除來源表格。
將中介表格重新命名為來源表格的名稱。

如果紀錄存在於 PostgreSQL 中，我該如何刪除它？

如果紀錄存在於 PostgreSQL 中，您可以透過使用包含 EXISTS 的 WHERE 子句的 DELETE 陳述式來刪除它。EXISTS 子句需要子查詢。

基本語法如下所示

DELETE FROM table_name
    WHERE EXISTS ( subquery );

如何在 PostgreSQL 中刪除有限數量的紀錄？

PostgreSQL 僅允許其 SELECT 陳述式中使用 LIMIT 子句。因此，為了在 DELETE 陳述式中使用它，您必須包含 SELECT。

語法可能看起來像這樣

DELETE FROM table_name
WHERE field_name IN (
    SELECT field_name FROM table_name LIMIT 1);

關於作者

Justin Ellingwood

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

使用 grant 和 revoke 管理 PostgreSQL 中的權限

如何在 PostgreSQL 中更新現有資料

在 GitHub 上編輯此頁面

PostgreSQL / 插入和修改資料

如何在 PostgreSQL 中插入和刪除資料

內容

簡介

檢閱表格結構

使用 INSERT 將新紀錄新增至表格

從 INSERT 陳述式傳回資料

使用 INSERT 一次新增多列

使用 DELETE 從表格中移除列

從 DELETE 陳述式傳回資料

使用 DELETE 一次移除多列

結論

常見問題

Justin Ellingwood

Prisma 資料指南

使用 `INSERT` 將新紀錄新增至表格

從 `INSERT` 陳述式傳回資料

使用 `INSERT` 一次新增多列

使用 `DELETE` 從表格中移除列

從 `DELETE` 陳述式傳回資料

使用 `DELETE` 一次移除多列