PostgreSQL: 複合プライマリーキー

次のように、複合プライマリーキー（複合主キー）があるテーブルの取り扱いです。

Create

create.sql

create table cities (id_pref varchar(10) not null, id_city varchar(10) not null,
	name text, population int, date_mod date,
	primary key (id_pref,id_city));

実行結果

city=# \d cities
                        Table "public.cities"
   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 id_pref    | character varying(10) |           | not null | 
 id_city    | character varying(10) |           | not null | 
 name       | text                  |           |          | 
 population | integer               |           |          | 
 date_mod   | date                  |           |          | 
Indexes:
    "cities_pkey" PRIMARY KEY, btree (id_pref, id_city)

Insert

insert.sql

insert into cities values ('p34','c01','広島',72814,'2001-9-14');
insert into cities values ('p34','c02','福山',41738,'2001-7-21');
insert into cities values ('p34','c03','東広島',92513,'2001-6-12');

実行結果

city=# select * from cities;
 id_pref | id_city |  name  | population |  date_mod  
---------+---------+--------+------------+------------
 p34     | c01     | 広島   |      72814 | 2001-09-14
 p34     | c02     | 福山   |      41738 | 2001-07-21
 p34     | c03     | 東広島 |      92513 | 2001-06-12
(3 rows)

Upsert

upsert01.sql

INSERT INTO cities (id_pref,id_city,name,population,date_mod)
    VALUES ('p35','c01','山口',23541,'2022-5-10'),
	('p35','c02','下関',83762,'2022-5-10'),
	('p35','c03','宇部',31725,'2022-5-10')
    ON CONFLICT (id_pref,id_city) DO UPDATE SET name = EXCLUDED.name,
	 population = EXCLUDED.population,
	 date_mod = EXCLUDED.date_mod;

実行結果

city=# select * from cities;
 id_pref | id_city |  name  | population |  date_mod  
---------+---------+--------+------------+------------
 p34     | c01     | 広島   |      72814 | 2001-09-14
 p34     | c02     | 福山   |      41738 | 2001-07-21
 p34     | c03     | 東広島 |      92513 | 2001-06-12
 p35     | c01     | 山口   |      23541 | 2022-05-10
 p35     | c02     | 下関   |      83762 | 2022-05-10
 p35     | c03     | 宇部   |      31725 | 2022-05-10
(6 rows)

upsert02.sql

INSERT INTO cities (id_pref,id_city,name,population,date_mod)
    VALUES ('p35','c01','山口',9923541,'2024-7-11'),
	('p35','c02','下関',9983762,'2024-7-11'),
	('p35','c03','宇部',9931725,'2024-7-11')
    ON CONFLICT (id_pref,id_city) DO UPDATE SET name = EXCLUDED.name,
	 population = EXCLUDED.population,
	 date_mod = EXCLUDED.date_mod;

実行結果

city=# select * from cities;
 id_pref | id_city |  name  | population |  date_mod  
---------+---------+--------+------------+------------
 p34     | c01     | 広島   |      72814 | 2001-09-14
 p34     | c02     | 福山   |      41738 | 2001-07-21
 p34     | c03     | 東広島 |      92513 | 2001-06-12
 p35     | c01     | 山口   |    9923541 | 2024-07-11
 p35     | c02     | 下関   |    9983762 | 2024-07-11
 p35     | c03     | 宇部   |    9931725 | 2024-07-11
(6 rows)

Python で実装した例

composite_upsert.py

#! /usr/bin/python
#
#	composite_upsert.py
#
#				Aug/20/2024
#
# --------------------------------------------------------
import sys
import os
import psycopg2
from dotenv import load_dotenv
#
#
# --------------------------------------------------------
def sql_upsert_string_gen_proc ():
	insert_sql = '''
	INSERT INTO cities (id_pref, id_city, name, population, date_mod)
	VALUES (%s, %s, %s, %s, %s)
	ON CONFLICT (id_pref, id_city) DO UPDATE SET
	(name, population, date_mod) = (EXCLUDED.name, EXCLUDED.population, EXCLUDED.date_mod);
'''
	return	insert_sql
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
#
dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
conn = psycopg2.connect("dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD)
#
cur = conn.cursor()
#
sql_str=sql_upsert_string_gen_proc()
print(sql_str)
cur.execute(sql_str,('p35','c01','山口',113549,'2024-8-17'))
cur.execute(sql_str,('p35', 'c02','下関',113762,'2024-8-17'))
cur.execute(sql_str,('p35', 'c03','宇部',111725,'2024-8-17'))
conn.commit ()
#
cur.close ()
conn.close ()
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------