なんとなくあるんだろうとは思ってたけど、郵便番号のデータ公開されているなんて知らなかった。PostgreSQLの操作にも疎いので読んでみた。
CREATE TABLEしてテーブル用意したあと、
\copy [テーブル名] from utf_ken_all.csv (format csv)
でデータをロードできるとあるけど、テーブル作るときに地方公共団体コードの前にidカラムを追加しちゃったのでうまく行かない。しょうがないのでスクリプト書いてロードする。
import csv
import psycopg
stmt = """
INSERT INTO postoffice_zipcode (lgcode,old_code,new_code,pref_kana,city_kana,
town_kana,pref,city,town,large_area,koaza,choume,small_area,change_flag,reason)
VALUES (%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s);
"""
def connect_db():
return psycopg.connect("dbname=db user=admin password=secret host=localhost")
def insert_row(cur, row):
cur.execute(stmt,
(row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10], row[11],
row[12], row[13], row[14])
)
def main():
conn = connect_db()
cur = conn.cursor()
with open("utf_ken_all.csv", "r") as f:
reader = csv.reader(f)
for row in reader:
insert_row(cur, row)
conn.commit()
cur.close()
if __name__ == "__main__":
main()
その他、覚書。
主な操作 (説明不要)
sudo -u postgres -i psql
create user admin;
create database [データベース名] owner admin;
createdb [データベース名]
psql [データベース名] -h localhost -U admin
psql [データベース名] -h localhost -U admin -f [ファイル名]
SELECT * FROM postoffice_zipcode WHERE CHAR_LENGTH(town_kana) >= 300;
WHERE句の条件式
| operator | description |
|---|---|
| = | equal to |
| <> | not equal to |
| > | greater than |
| < | less than |
| >= | grater than or equal to |
| <= | less than or equal to |
| BETWEEN | range condition |
| LIKE | pattern match |
| IN | multiple values |
| IS NULL | null check |
| EXISTS | subquery condition |
| ALL | comparison with subquery |
| ANY | comparison with subquery |
| % | matches any number of chars |
| _ | matches a single char |
integer型
-2147483648+2147483647までの整数
小数点以下は自動で四捨五入
numeric型
任意の精度の数値データ型
小数部分: 131072桁
整数部分: 131073桁
まで指定可能
numeric(precision, scale)
precision: 総桁数
scale: 小数部の桁数
numeric(6,2)の場合､-9999.99から9999.99までの値を格納可能
postgresではdecimalとnumericは同じ型
varchar型
上限ありの可変長
全角・半角は同じ文字数の扱い
text型
上限なしの可変長
日付と時刻
日付と時刻関連の関数
SHOW DATESTYLE;
SELECT '2024-01-01'::date;
SELECT 'Mar,13 2024'::date;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT now();
JOIN
説明不要の結合
LEFT(RIGHT) OUTER JOIN
結合の左側 (右側) のテーブルがすべて検索結果に含まれる。
FULL OUTER JOINも同様の考え。
CROSS JOIN
クロス集計。結合するテーブルのすべての組み合わせ。
自己結合
1つのテーブルを2つのテーブルに見立てて結合する。
主キー
UNIQUEかつNOT NULLの制約でテーブル内のレコードを一意に特定する。
ALTER TABLE [テーブル名] ADD PRIMARY KEY(列);
複数列の主キー
ALTER TABLE [テーブル名] ADD PRIMARY KEY(列1, 列2,...);
外部キー
その列の値が他のテーブルの主キーに存在する列のこと。
ALTER TABLE [参照元のテーブル名] ADD FOREIGN KEY (外部キーを設定する列) REFERENCES [参照先のテーブル名](列);
CREATE TABLE [テーブル名] (列名 データ型 PRIMARY KEY, ...);
NULL
空文字''とは異なる、データがない状態
SELECT count(*)ではNULLを含むレコードは検索結果に含まれる
シーケンス
連番を作る。完全な連番は保証しない。
CREATE SEQUENCE [シーケンス名];
SELECT currval('シーケンス名');
SELECT nextval('シーケンス名');
SELECT setval('シーケンス名', n);