PostgreSQL で住所マスタを作る 〜郵便住所.jp の CSV を使って〜

testing-postgresql-docker/
├── docker-compose.yml
├── csv/            ← ダウンロードした CSV を置く
└── initdb/         ← 起動時に実行される SQL を置く

C:\Users\Kuma> mkdir testing-postgresql-docker
C:\Users\Kuma> cd ./testing-postgresql-docker
C:\Users\Kuma\testing-postgresql-docker> mkdir csv
C:\Users\Kuma\testing-postgresql-docker> mkdir initdb

testing-postgresql-docker/
└── csv/
    ├── prefecture.csv
    ├── municipality.csv
    └── postal_address.csv

prefecture.csv
"01","北海道","ホッカイドウ","HOKKAIDO"
"02","青森県","アオモリケン","AOMORI KEN"

municipality.csv
"01101","札幌市中央区","サッポロシチュウオウク","SAPPORO SHI CHUO KU","01"
"01102","札幌市北区","サッポロシキタク","SAPPORO SHI KITA KU","01"

postal_address.csv
"0010010","01102","北十条西（１～４丁目）","キタ１０ジョウニシ（１－４チョウメ）","KITA10-JONISHI(1-4-CHOME)"
"0010011","01102","北十一条西（１～４丁目）","キタ１１ジョウニシ（１－４チョウメ）","KITA11-JONISHI(1-4-CHOME)"

services:
  postgres:
    image: postgres:17
    container_name: testing-postgresql
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postaladdress
      TZ: Asia/Tokyo
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./initdb:/docker-entrypoint-initdb.d
      - ./csv:/csv

volumes:
  postgres-data:

CREATE DATABASE postaladdress ENCODING 'UTF8';

CREATE TABLE prefecture (
    prefecture_code      char(2) NOT NULL,
    prefecture_name      text    NOT NULL,
    prefecture_name_kana text    NOT NULL,
    prefecture_name_rome text    NOT NULL,
    CONSTRAINT pk_prefecture PRIMARY KEY (prefecture_code)
);

COMMENT ON TABLE  prefecture                      IS '都道府県';
COMMENT ON COLUMN prefecture.prefecture_code      IS '都道府県コード';
COMMENT ON COLUMN prefecture.prefecture_name      IS '都道府県名';
COMMENT ON COLUMN prefecture.prefecture_name_kana IS '都道府県名カナ';
COMMENT ON COLUMN prefecture.prefecture_name_rome IS '都道府県名ローマ字';

CREATE TABLE municipality (
    municipality_code      char(5) NOT NULL,
    municipality_name      text    NOT NULL,
    municipality_name_kana text    NOT NULL,
    municipality_name_rome text,
    prefecture_code        char(2) NOT NULL,
    CONSTRAINT pk_municipality PRIMARY KEY (municipality_code),
    CONSTRAINT fk_municipality_prefecture FOREIGN KEY (prefecture_code)
        REFERENCES prefecture (prefecture_code)
);

COMMENT ON TABLE  municipality                        IS '市区町村';
COMMENT ON COLUMN municipality.municipality_code      IS '市区町村コード';
COMMENT ON COLUMN municipality.municipality_name      IS '市区町村名';
COMMENT ON COLUMN municipality.municipality_name_kana IS '市区町村名カナ';
COMMENT ON COLUMN municipality.municipality_name_rome IS '市区町村名ローマ字';
COMMENT ON COLUMN municipality.prefecture_code        IS '都道府県コード';

CREATE TABLE postal_address (
    id                bigint GENERATED ALWAYS AS IDENTITY,
    postal_code       char(7) NOT NULL,
    municipality_code char(5) NOT NULL,
    street_name       text    NOT NULL,
    street_name_kana  text    NOT NULL,
    street_name_rome  text,
    CONSTRAINT pk_postal_address PRIMARY KEY (id),
    CONSTRAINT fk_postal_address_municipality FOREIGN KEY (municipality_code)
        REFERENCES municipality (municipality_code)
);

COMMENT ON TABLE  postal_address                   IS '郵便住所';
COMMENT ON COLUMN postal_address.id                IS 'ID';
COMMENT ON COLUMN postal_address.postal_code       IS '郵便番号';
COMMENT ON COLUMN postal_address.municipality_code IS '市区町村コード';
COMMENT ON COLUMN postal_address.street_name       IS '町域名';
COMMENT ON COLUMN postal_address.street_name_kana  IS '町域名カナ';
COMMENT ON COLUMN postal_address.street_name_rome  IS '町域名ローマ字';

CREATE INDEX idx_municipality_prefecture_code ON municipality (prefecture_code);
CREATE INDEX idx_postal_address_postal_code   ON postal_address (postal_code);

\copy prefecture FROM '/csv/prefecture.csv' WITH (FORMAT csv, ENCODING 'UTF8')
\copy municipality (municipality_code, municipality_name, municipality_name_kana, municipality_name_rome, prefecture_code) FROM '/csv/municipality.csv' WITH (FORMAT csv, ENCODING 'UTF8', FORCE_NULL (municipality_name_rome))
\copy postal_address (postal_code, municipality_code, street_name, street_name_kana, street_name_rome) FROM '/csv/postal_address.csv' WITH (FORMAT csv, ENCODING 'UTF8', FORCE_NULL (street_name_rome))

\copy postal_address (postal_code, municipality_code, street_name, street_name_kana, street_name_rome) FROM ...

"0130310","05203","大雄佐加里西","タイユウサガリニシ",""

WITH (FORMAT csv, ENCODING 'UTF8', FORCE_NULL (street_name_rome))

testing-postgresql-docker/
├── docker-compose.yml
├── csv/
│   ├── prefecture.csv
│   ├── municipality.csv
│   └── postal_address.csv
└── initdb/
    ├── 01_create_table.sql
    └── 02_import_csv.sql

C:\Users\Kuma\testing-postgresql-docker> docker compose up -d

C:\Users\Kuma\testing-postgresql-docker> docker compose logs postgres

C:\Users\Kuma\testing-postgresql-docker> docker compose exec postgres psql -U postgres -d postaladdress

postaladdress=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | municipality   | table | postgres
 public | postal_address | table | postgres
 public | prefecture     | table | postgres
(3 rows)

postaladdress=# SELECT count(*) FROM prefecture;
 count
-------
    47
(1 row)

postaladdress=# SELECT count(*) FROM municipality;
 count
-------
  1892
(1 row)

postaladdress=# SELECT count(*) FROM postal_address;
 count
--------
 122653
(1 row)

SELECT prefecture_code, prefecture_name
  FROM prefecture
 ORDER BY prefecture_code;

 prefecture_code | prefecture_name
-----------------+-----------------
 01              | 北海道
 02              | 青森県
 03              | 岩手県
 ...
 47              | 沖縄県
(47 rows)

SELECT m.municipality_code, m.municipality_name
  FROM municipality m
  JOIN prefecture p ON p.prefecture_code = m.prefecture_code
 WHERE p.prefecture_name = '東京都'
 ORDER BY m.municipality_code;

 municipality_code | municipality_name
-------------------+-------------------
 13101             | 千代田区
 13102             | 中央区
 13103             | 港区
 ...
(62 rows)

SELECT a.postal_code,
       p.prefecture_name,
       m.municipality_name,
       a.street_name
  FROM postal_address a
  JOIN municipality m ON m.municipality_code = a.municipality_code
  JOIN prefecture   p ON p.prefecture_code   = m.prefecture_code
 WHERE a.postal_code = '1000001';

 postal_code | prefecture_name | municipality_name | street_name
-------------+-----------------+-------------------+-------------
 1000001     | 東京都          | 千代田区          | 千代田
(1 row)

SELECT a.postal_code,
       p.prefecture_name,
       m.municipality_name,
       a.street_name
  FROM postal_address a
  JOIN municipality m ON m.municipality_code = a.municipality_code
  JOIN prefecture   p ON p.prefecture_code   = m.prefecture_code
 WHERE m.municipality_name = '千代田区'
   AND a.street_name LIKE '大手町%'
 ORDER BY a.postal_code;

 postal_code | prefecture_name | municipality_name |           street_name
-------------+-----------------+-------------------+----------------------------------
 1000004     | 東京都          | 千代田区          | 大手町（次のビルを除く）
 1006801     | 東京都          | 千代田区          | 大手町ＪＡビル（１階）
 1006802     | 東京都          | 千代田区          | 大手町ＪＡビル（２階）
 ...
 1006890     | 東京都          | 千代田区          | 大手町ＪＡビル（地階・階層不明）
(39 rows)

SELECT p.prefecture_name,
       count(*) AS address_count
  FROM postal_address a
  JOIN municipality m ON m.municipality_code = a.municipality_code
  JOIN prefecture   p ON p.prefecture_code   = m.prefecture_code
 GROUP BY p.prefecture_code, p.prefecture_name
 ORDER BY address_count DESC
 LIMIT 5;

 prefecture_name | address_count
-----------------+---------------
 北海道          |          8002
 愛知県          |          7666
 京都府          |          6421
 新潟県          |          5451
 兵庫県          |          5179
(5 rows)

SELECT postal_code, count(*) AS street_count
  FROM postal_address
 GROUP BY postal_code
HAVING count(*) > 1
 ORDER BY street_count DESC
 LIMIT 5;

 postal_code | street_count
-------------+--------------
 4520961     |           66
 4801103     |           65
 4410302     |           46
 7793405     |           44
 0294205     |           42
(5 rows)

SELECT postal_code, street_name
  FROM postal_address
 WHERE postal_code = '4520961'
 ORDER BY street_name
 LIMIT 3;

 postal_code | street_name
-------------+-------------
 4520961     | 春日一本松
 4520961     | 春日一番割
 4520961     | 春日三番割
(3 rows)

SELECT count(*)
  FROM postal_address
 WHERE street_name_rome IS NULL;

 count
-------
   177
(1 row)

C:\Users\Kuma\testing-postgresql-docker> docker compose down -v
C:\Users\Kuma\testing-postgresql-docker> docker compose up -d

ERROR:  value too long for type character(5)
CONTEXT:  COPY postal_address, line 1, column municipality_code: "北十条西（１～４丁目）"

C:\Users\Kuma> chcp 65001