PostgreSQL SQL構文(DML):INSERT文
INSERT
文は、テーブルに新しいレコードを追加するSQL文です。
INSERT文の基本構文
INSERT INTO テーブル名 (列1, 列2, 列3, ...)
VALUES (値1, 値2, 値3, ...);
INSERT INTO
レコードを追加するテーブル名を指定します。
VALUES
各列に設定する値を指定します。
usersテーブルにレコードを追加する基本的なINSERT文を以下に示します。
INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 25);
usersテーブルの、各列(name, email, age)に値(’Taro Yamada’, ‘taro@example.com’, 25)を設定したレコードを追加します。数値型と論理値はそのままで値を指定しますが、それ以外のデータ型は単一引用符(’)で囲みます。
複数レコードを一度に追加する
複数のレコードを一度に追加することもできます。
INSERT INTO users (name, email, age)
VALUES
('Taro Yamada', 'taro@example.com', 25),
('Hanako Suzuki', 'hanako@example.com', 30),
('Jiro Tanaka', 'jiro@example.com', 22);
‘Taro Yamada’、’Hanako Suzuki’、’Jiro Tanaka’ の3人を同時に追加しています。
列名を省略
テーブルのすべての列に値を挿入する場合は、列名を省略できます。
INSERT INTO users
VALUES ('Taro Yamada', 'taro@example.com', 25);
SELECTを使ったレコードの挿入
他のテーブルやクエリの結果を使ってデータを挿入することもできます。
INSERT INTO users (name, email, age)
SELECT name, email, age FROM new_users WHERE active = true;
new_users テーブルから有効なレコードを、usersテーブルに追加します。
ON CONFLICT(UPSERT)重複データの処理
レコード追加時に値が重複した場合、値を変更するか、スキップする事ができます。
値を更新する(UPDATE)
ON CONFLICT でデータ(emailアドレス等)が重複した場合、重複したデータは、DO UPDATE SET により、VALUES で指定された値に更新されます。
INSERT INTO users (id, name, email, age)
VALUES (1, 'Taro Yamada', 'taro@example.com', 25)
ON CONFLICT (id)
DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email,
age = EXCLUDED.age;
既に以下のデータが挿入されていると仮定します。
INSERT INTO users (id, name, email, age)
VALUES (1, 'Taro Yamada', 'taro@example.com', 25);
この時、下記のSQL文を実行すると、
INSERT INTO users (id, name, email, age)
VALUES (2, 'Hanako Suzuki', 'taro@example.com', 30)
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
age = EXCLUDED.age;
ON CONFLICT で指定した、emailが重複しているので、DO UPDATE SET で指定されている、id=1 のnameとageの値が変更されます。
挿入をスキップする(DO NOTHING)
挿入をスキップ してエラーも表示せず、何もしません。
INSERT INTO users (id, name, email, age)
VALUES (2, 'Hanako Suzuki', 'taro@example.com', 30)
ON CONFLICT (email)
DO NOTHING;
DO NOTHING; の指定により、id=1 のnameとageの値は変更しません。
RETURNING
INSERT
文の後に RETURNING
句を使うことで、挿入された行の特定の列を取得することができます。
INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 25)
RETURNING id;
追加されたレコードの「id」を取得できます。
RETURNING idの取得方法「Pythonの場合」
INSERT文を実行した後、cur.fetchone()メソッドで取得します。
import psycopg2
# データベースに接続
conn = psycopg2.connect(
host="localhost",
database="your_database",
user="your_username",
password="your_password"
)
# カーソルを作成
cur = conn.cursor()
# INSERT文の実行とRETURNING句を使ったID取得
insert_query = """
INSERT INTO users (name, email, age)
VALUES (%s, %s, %s)
RETURNING id;
"""
# 挿入するデータ
data = ('Taro Yamada', 'taro@example.com', 25)
# クエリの実行
cur.execute(insert_query, data)
# id を取得
inserted_id = cur.fetchone()[0]
# 結果の確認
print(f"挿入された行のIDは: {inserted_id}")
# トランザクションをコミット
conn.commit()
# カーソルと接続を閉じる
cur.close()
conn.close()
- データベース接続:
psycopg2.connect()
でデータベースに接続します。 - カーソル作成:
conn.cursor()
でデータベース操作を行うカーソルを作成します。 - クエリ作成:
INSERT
文にRETURNING id
を含めます。これにより、挿入された行のid
を返します。 - クエリ実行:
cur.execute(insert_query, data)
でクエリを実行し、data
タプルをプレースホルダ%s
に渡します。 id
を取得:cur.fetchone()
でクエリの結果(RETURNING id
によって返されたid
)を取得し、リスト形式で受け取った最初の要素をinserted_id
に格納します。- コミット:
conn.commit()
でトランザクションを確定します。 - 接続終了:最後にカーソルとデータベース接続を閉じます。
INSERT文のまとめ
- データベースにデータを追加するために使われるSQL文である。
- 複数レコードを一度に追加する事ができる。
- レコードの列全てに値を設定する場合は、列名を省略できる。
- 重複データがある場合、重複レコード内の値を変更する事も、変更しない事もできる。
- RETURNING を使用する事で、追加されたレコードの特定の値を取得する事ができる。