昨日の続きです。昨日は簡単なINSERT文でテストしましたが、今日は実践的なデータエクスポート。PostgreSQLにあるテーブルから、必要な列だけSQLiteにINSERTできました。ただ何故かTEXT型の列をconcat関数でラップする必要があり、でないとPostgreSQLサーバが落ちます。。。便利な反面、ちょっと注意がいると判明。
前回：実行環境 ～OGR_FDWインストール ～ テスト

SQLiteにエクスポートするテーブル

↓ 旧ブログ（Seesaa）の記事リスト。ここから日付・タイトルなど4列をSQLiteにエクスポートして記事検索に使いたい。現在もすでにSQLiteで稼動してますが、それはpg_dump経由でエクスポートしたもの。手順が少々面倒でした（詳細は8月31日の記事）。今日は、同じDBをOGR_FDWで作ってみます。 
# \d kenpg_seesaa_list
Table "public.kenpg_seesaa_list"
+-----------+------+-----------+
|  Column   | Type | Modifiers |
+-----------+------+-----------+
| ymd       | date | not null  | <=
| url       | text | not null  |
| title     | text | not null  | <=
| tags      | text | not null  | <=
| html_file | text | not null  |
| html      | text | not null  |
| body      | text |           | <=
+-----------+------+-----------+
Indexes:
    "kenpg_seesaa_list_pkey" PRIMARY KEY, btree (ymd) 
    "kenpg_seesaa_list_url_idx" UNIQUE, btree (url)

手順まとめ

OGR_FDWのセットアップまでは昨日書いたので省き、今日は（1）エクスポート先DBとテーブルをSQLiteで作成、（2）SQLiteテーブルをPostgreSQLの外部テーブル化、（3）既存テーブルからINSERT、の三つ。 まとめると ↓ うち1と2は昨日も簡単なテストで行いました。新しく、また普通と違うのはINSERTするSELECTクエリで、TEXT型の列をわざわざconcat関数でラップしてます。でないとINSERTに失敗しサーバが落ちるため。 
-- 1) create sqlite database
$ sqlite3 r:/seesaa_ogrfdw.db
sqlite> CREATE TABLE articles (ymd, title, tags, body2); 

-- 2) create a foreign table on psql
# create server seesaa_ogrfdw
    foreign data wrapper ogr_fdw
    options (
        datasource 'r:/seesaa_ogrfdw.db',
        format 'SQLite');

# import foreign schema ogr_all
    from server seesaa_ogrfdw
    into public;

-- 3) export data to the foreign table
# insert into public.articles (ymd, title, tags, body2) 
    select ymd,
        concat(title), 
        concat(tags),
        concat(body2)
    from kenpg_seesaa_list;
上の（1）SQLiteでテーブルを作る際、列の型指定を省いてます。昨日テストした範囲では、指定しても結局テキストになるっぽいので。以下、psqlに移って2と3を実際やってみた様子。

psqlでの実際

まず外部テーブルを入れるサーバの作成。SQLiteのファイル一つを関連付けます。ファイルがないとクエリがエラー。フォーマット名はSQLiteと3が付きませんが、3です。 
# create server seesaa_ogrfdw
    foreign data wrapper ogr_fdw
    options (
        datasource 'r:/seesaa_ogrfdw.db',
        format 'SQLite');

# \des+
                                                                  List of foreign servers
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+ 
|     Name      |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                     FDW Options                     | Description |
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+
| seesaa_ogrfdw | postgres | ogr_fdw              |                   |      |         | (datasource 'r:/seesaa_ogrfdw.db', format 'SQLite') |             |
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+
(1 row)
続いてIMPORTA FOREIGN SCHEMA文で外部テーブル作成。普通のCREATE FOREIGN TABLEでも可能ですが、昨日書いたとおりこの方が便利なので。 
# import foreign schema ogr_all
    from server seesaa_ogrfdw
    into public;

# \det+ 
                         List of foreign tables
+--------+----------+---------------+--------------------+-------------+ 
| Schema |  Table   |    Server     |    FDW Options     | Description |
+--------+----------+---------------+--------------------+-------------+
| public | articles | seesaa_ogrfdw | (layer 'articles') |             |
+--------+----------+---------------+--------------------+-------------+
(1 row)
以上でエクスポート準備終わり。INSERTする前の確認で、エクスポート元（普通のテーブル）と先（外部テーブル）の列構成を表示します。↓ 
# \d kenpg_seesaa_list
Table "public.kenpg_seesaa_list"
+-----------+------+-----------+
|  Column   | Type | Modifiers |
+-----------+------+-----------+
| ymd       | date | not null  | 
| url       | text | not null  |
| title     | text | not null  |
| tags      | text | not null  |
| html_file | text | not null  |
| html      | text | not null  |
| body      | text |           |
+-----------+------+-----------+
 

# \d articles
            Foreign table "public.articles"
+--------+-------------------+-----------+-------------+
| Column |       Type        | Modifiers | FDW Options |
+--------+-------------------+-----------+-------------+
| fid    | bigint            |           |             |
| ymd    | character varying |           |             |
| title  | character varying |           |             |
| tags   | character varying |           |             |
| body2  | character varying |           |             |
+--------+-------------------+-----------+-------------+
Server: seesaa_ogrfdw
FDW Options: (layer 'articles')
↓ 外部テーブルが空の状態から、INSERT文を実行。元テーブルの693行が外部テーブル = SQLiteのデータベースに書き込まれました。 
# select * from articles;
+-----+-----+-------+------+-------+
| fid | ymd | title | tags | body2 |
+-----+-----+-------+------+-------+
+-----+-----+-------+------+-------+
(0 rows)

# insert into articles (ymd, title, tags, body2) 
    select ymd,
        concat(title), 
        concat(tags),
        concat(body2)
    from kenpg_seesaa_list;
INSERT 0 693

# select count(*) from articles;

結果確認：pg_dump経由でエクスポートしたものと比較

↓ 書き込んだテーブルの行数をSQLiteクライアントで出すと確かに693行。なおPostgreSQLでの外部テーブル化に関わらず、SQLiteクライアントからは普通にアクセスできます。またpg_dump経由でエクスポートしたものと同一サイズ。テーブル定義が若干違うためか（今回は型を省略）中身は少し違うようです。 
$ sqlite3 r:/seesaa_ogrfdw.db
sqlite> select count(*) from articles;
693

$ ls -l r:/*.db
-rwxrwx---+ 1 Administrators None 3993600 Aug 30 08:46 seesaa.db
-rwxrwx---+ 1 Administrator  None 3993600 Sep  4 20:04 seesaa_ogrfdw.db <= 

$ diff --binary seesaa.db seesaa_ogrfdw.db
Binary files seesaa.db and seesaa_ogrfdw.db differ
テーブルの中身も、日本語やローマ数字を含め問題なく前と同様にエクスポートされてました。で、既に旧ブログ記事の検索で使ってます。前の「INSERT文でpg_dump → エディタで開いて編集 → SQLiteクライアントでインポート」より確実に便利。

既存テーブルのTEXT列をそのままINSERTするとサーバが落ちる

これは残念な、かつ不可解な点。既存テーブルにあるTEXT型の列をそのままINSERTでSQLiteの外部テーブルに入れようとしたら ↓ サーバが異常終了します。下の赤い部分はpsqlで接続が切れた際のメッセージ。 
# insert into articles (ymd, title, tags, body2)
    select ymd,
        title,
        tags,
        body2
    from kenpg_seesaa_list;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
↓ こちらがサーバのログ。詳しくは今後調べます。 
LOG:  server process (PID 4848) was terminated by exception 0xC0000005
DETAIL:  Failed process was running: insert into articles (ymd, title, tags, body2)
            select ymd,
                title,
                tags,
                body2
            from kenpg_seesaa_list;
HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
...
TEXT型でない列、今回では日付型のymdはそのままINSERTできてます。なぜconcat関数にくるむとOKなのか謎。型キャストでTEXTやVARCHARにしても駄目でした。 とは言え、SQLite専用でなく多様なGISデータが主眼のOGR_FDWで、外部テーブルに書き込み可能になった最初のバージョンで一応ここまで使えたのは予想以上。今後いろいろ試したいと思います。