PostgreSQLでデータを挿入および削除する方法

はじめに

テーブルへのレコードの追加と削除は、データベースが実行する最も一般的な操作の一部です。データの追加には、値を追加するテーブルと列の名前、および各フィールドに入力する値を指定する必要があります。レコードの削除には、正しい行を特定してテーブルから削除する必要があります。

このガイドでは、PostgreSQLでSQLのINSERTコマンドとDELETEコマンドを使用する方法について説明します。これには、基本的な構文、処理されたデータに関するデータ情報を返す方法、および単一のステートメントで複数行を追加または削除する方法が含まれます。

テーブル構造の確認

INSERTコマンドを使用する前に、テーブルのデータ型と制約、列によって課せられる要件に対応できるように、テーブルの構造を知っておく必要があります。データベースクライアントに応じて、これを行うにはいくつかの異なる方法があります。

psqlコマンドラインクライアントを使用している場合、この情報を見つける最も簡単な方法は、ツールに組み込まれている\d+メタコマンドを使用することです。

たとえば、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+メタコマンドは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)

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を使用して一度に複数の行を追加する

レコードを1つのステートメントずつ挿入するよりも、一度に複数行を挿入する方が時間がかからず効率的です。PostgreSQLでは、同じテーブルに追加する複数の行を指定できます。新しい各行は括弧で囲まれ、括弧の各セットはカンマで区切られます。

複数レコード挿入の基本的な構文は次のようになります。

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

参照しているemployeeテーブルの場合、次の入力をタイプすることで、1つのステートメントで4人の新しい従業員を追加できます。

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>;

たとえば、first_nameがAbigailに設定されているemployeeテーブルのすべての行に対して、次のように入力できます。

DELETE FROM employee
WHERE first_name = 'Abigail';

DELETE 1

ここでの戻り値は、DELETEコマンドが処理され、1行が削除されたことを示しています。

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コマンドほど効率的ではありません。これは、テーブルをスキャンせずにデータを削除できます。

結論

この記事では、PostgreSQLテーブルのデータを制御するための最も重要なコマンドのいくつかを紹介しました。INSERTコマンドはテーブルに新しいデータを追加するために使用でき、DELETEコマンドは削除する必要のある行を指定します。どちらのコマンドも、影響を与える行を返すことができ、一度に複数行を操作できます。

これら2つのコマンドは、テーブルに含まれるレコード数を増減するために使用される主要なメカニズムです。基本的な構文と、他の句と組み合わせることができる方法を理解することで、必要に応じてテーブルにデータを入力およびクリーンアップできます。

FAQ

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');

WHERE EXISTS ( subquery );

DELETE FROM table_name
    WHERE EXISTS ( subquery );

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