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テーブルの場合、次のように入力することで、単一のステートメントで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>;

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

DELETE FROM employee
WHERE first_name = 'Abigail';

DELETE 1

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

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