はじめに

『オープンソースデータベース標準教科書 -PostgreSQL-（Ver.2.0.0）Kindle版』を使ってPostgreSQLの操作を学習中です。

この教科書の
4.演習　--4.2演習2:郵便番号データベース
を進めています。

つまずいたこと

DB(DBeaver/PostgreSQL)にCSVファイル（郵便番号データ）をインポートしDB内の「テーブル zip」のレコードデータにしたい。が、エラーになってインポートできない。

環境

ホストPC：Windows10
GUI DBツール：DBeaver
仮想化ソフト：VirtualBox
ゲストPC：Vagrant(＝CentOS7＝仮想マシン）
コンテナ型仮想環境：Docker
CUI DBクライアント：PostgreSQL

エラーが出るまでの流れ

1）テーブル作成（郵便番号データ用の表定義）

PostgreSQL(DBeaverのSQLエディタに書く)

CREATE TABLE zip (
lgcode char(5), oldzip char(5), newzip char(7), 
prefkana text, citykana text, areakana text,
pref text, city text, area text,
largearea integer, koaza integer, choume integer,smallarea integer,
change integer, reason integer
);

（引用元：宮原徹; 喜田紘一. オープンソースデータベース標準教科書 -PostgreSQL-（Ver.2.0.0） 
(Kindle の位置No.1327-1331). LPI-Japan. Kindle 版.）

全部で15カラム。（カラム＝列）
実行画面↓

↓
first_turorialの上で右クリック
↓
「更新」をクリック
↓
zipテーブルが表示された！

実行成功！

2）郵便番号csvデータを用意

一）郵便局HPから下記をダウンロード
「住所の郵便番号（CSV形式）
読み仮名データの促音・拗音を小書きで表記するもの（例：ホッカイドウ）
全国一括」

二）郵便番号データの文字コードをANSIからUTF-8へ変更
郵便番号csvデータのファイルを右クリック
↓
プログラムから開く
↓
「メモ帳」を選択
↓
ファイル
↓
名前を付けて保存
↓
文字コードをANSIからUTF-8に変更して「保存」
（PostgreSQLの文字コードがUTF-8なので、そちらに合わせた。合わせないと、PostgreSQLで扱うときに文字化けしてしまう。）

3）DBeaverでcsvをインポート

DBeaverのDBナビゲーター中のzipテーブルの上で右クリック
↓
「データのインポート」

↓

↓
「KEN_ALL.CSV」ファイルを選択して
↓
以下、ほぼデフォルトの状態で「開始 or 次へ」。

↓

↓

↓

※ここに「Data load setting - Transfer auto-generated columns」って書いてある！ ※「Do Commit after row insert:125000」の表記が気になる。デフォルトだと「Use Transactions」にチェックが付いていたが、外して「次へ」。 ↓

↓

4）エラー1 発生！！

【エラー文】 Error occurred during Create target metadata 理由: SQLエラー [42701]: ERROR: column "0" of relation "zip" already exists

【エラー文の意味】
ターゲットメタデータの作成中にエラーが発生しました。
SQLエラー42701（意味は「列の重複」）

「"zipテーブル" に "0カラム" を加えようとしているけど、もう既に存在してるよ」
という意味。同じ名前のカラムを同一のテーブルに2つ以上作れません。
（参考：Just do IT -2020/5/29記事）

・メタデータとは？→本体であるデータに関する付帯情報が記載されたデータ。データのためのデータ。
（参考：メタデータとは？データ管理に使えるメタデータの意味やメリットを解説！-Udemyメディア）

↓
とりあえず「×」
↓
下図の赤丸部分の数字が行数分（12万強）まで増えて...

↓

5）エラー2 発生！！

【エラー文】
Error occurred during batch insert
(you can disable batch insert in order to skip particular rows).

理由:
SQLエラー [42701]: バッチ 0 INSERT INTO first_tutorial.zip ("01101","060 ","0600000",ﾎｯｶｲﾄﾞｳ,ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ,ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ,北海道,札幌市中央区,以下に掲載がない場合,"0","0","0","0","0","0")
VALUES (1101,64.0,640941,'ﾎｯｶｲﾄﾞｳ','ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ','ｱｻﾋｶﾞｵｶ','北海道','札幌市中央区','旭ケ丘',0,0,1,0,0,0) はアボートしました: ERROR: column "0" specified more than once
位置: 122 このバッチの他のエラーは getNextException を呼び出すことで確認できます。

【エラー文の意味】
・バッチ挿入中にエラーが発生しました
（特定の行をスキップするためにバッチ挿入を無効にすることができます）。
・42701＝列の重複
・列「0」が複数回指定されている

→csvファイルの読み込みは、バッチ処理扱いになるのね。
→エラー1 同様「0」が指摘されている・・・。
↓
「スキップ」をクリックしてふと気づくと、
↓
列の中身がこうなっていました。

前回同様、必要な15カラム以外の赤枠部分のカラムが追加されてしまっています。
なぜこの部分がカラム扱いになってしまうのでしょうか？？
因みに、zipテーブルの中身は下画面のとおり「データなし」の状態です。

↓
ここで2通り、次に試す方法を考えました。

その1）DBeaverのDBナビゲータ内の「列」に表示されている、不要なカラムをすべて「削除」ボタンで削除する
せっかっくGUIを使っているのだし、DBeaverの機能を使って削除してみる。（OSS-DBの資格取得のための学習とは外れるが、ものは試しで。）

その2）TRUNCATEを実行し、そのうえでzipテーブルを削除する。その後あらためて一からzipテーブル作成する
前回作業の最後に、作成したzipテーブルをDBeaverの「削除」ボタンをクリックすることで削除した。その後、今回の作業を始めた。「削除」ボタンではなく、SQL文の「TRUNCATE(テーブルのすべての行を削除する)」を実行しなくてはならなかった？「削除」ボタンと「TRUNCATE」は同じ動作になるのか？動作に違いはあるのか？
（TRUNCATEについて参考：『OSS教科書 OSS-DB Silver Ver.2.0.対応』p212）

その1）DBeaverのDBナビゲータ内の「列」に表示されている、不要なカラムをすべて「削除」ボタンで削除する

まずはこの方法をやってみます。
ポチポチ削除して、DBeaverの「ファイル>上書き保存」をクリックすると
こんな画面が出ました。

「削除」をクリックすることで

ALTER TABLE first_tutorial.zip DROP COLUMN "01101";
ALTER TABLE first_tutorial.zip DROP COLUMN "060 ";
ALTER TABLE first_tutorial.zip DROP COLUMN "0600000";
ALTER TABLE first_tutorial.zip DROP COLUMN ﾎｯｶｲﾄﾞｳ;
ALTER TABLE first_tutorial.zip DROP COLUMN ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ;
ALTER TABLE first_tutorial.zip DROP COLUMN ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ;
ALTER TABLE first_tutorial.zip DROP COLUMN 北海道;
ALTER TABLE first_tutorial.zip DROP COLUMN 札幌市中央区;
ALTER TABLE first_tutorial.zip DROP COLUMN 以下に掲載がない場合;
ALTER TABLE first_tutorial.zip DROP COLUMN "0";

というSQL文が実行されていたことがわかりました。

（ALTER TABLE：テーブル定義の変更）

「持続する」をクリック。
↓

これで、求めていたzipテーブルの土台が出来上がりました。
次は、
①もう一度csvファイルをインポートして、レコードを挿入してみます。

それでもエラーが出たら、
その次は
②「エラー2」の原因を探してテーブルの内容を修正したうえで、
もう一度csvファイルをインポートして、レコードを挿入してみます。

黒澤さんからアドバイス

「Dbeaverのインポート機能に問題があると仮定した方が良いかも」
とアドバイスいただきました。

上記①②を試すのを取りやめ、まず、以前黒澤さんより
「そもそもlinux(Vagrant)上にcsvファイルが格納されていないのが問題では？
こういうのでファイルをアップロードできる」
と教えていただいた記事
（【Windows10版】Virtualbox × Vagrant WinSCPクライアントでFTP接続してみた。@silversink8888
）
を見ながら、WinSCPでCSVファイルをアップロードしてみます。

メモ

【今回使ったデータ型】

型名	説明
char(n)、character(n)	空白で埋められた固定長の文字列
text	可変長の文字列
integer、int	数値型。通常使用する整数。サイズは4バイト。範囲は-2147483648から+2147483647。
（参考：『OSS教科書　OSS-DB Silver Ver.2.0対応』p213-214）
（参考：PostgreSQL 8.3.7文書-第 8章データ型）

参考

・PostgreSQL 8.3.7文書　付録 A. PostgreSQLエラーコード