Symfoware

Symfowareについての考察blog

PostgreSQLのフィールドにハッシュ形式(hstore)でデータを登録する

PostgreSQLでは、hstoreというハッシュ形式でデータを保存するデータ型があるようなので試してみました。

試したのは、Arch LinuxにインストールしたPostgreSQL 9.1.3です。




テスト用のデータベースとテーブルの作成




postgresユーザーになり、sampleというデータベースを作成。
psqlを起動し、使用するデータベースをsampleに変更します。


# su - postgres
$ createdb -E UTF8 -T template0 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8 sample
$ psql
postgres=# \c sample;
You are now connected to database "sample" as user "postgres".





初期状態では、hstoreを使用することはできません。

CREATE EXTENSION hstore


というクエリを実行して、hstoreを使用できるようにします。



sample=# CREATE EXTENSION hstore;
WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
CREATE EXTENSION




idという数値型と、valueというhstore型のフィールドを持つtestテーブルを作成しました。


create table test (
id integer,
value hstore);








データの登録




"key" => "value"


という形式でデータを定義します。
もちろん、1フィールドに複数のkeyとvalueを保持できますので、カンマ区切りで複数のペアを
指定することができます。

とりあえず、こんなinsert文を実行して2レコードデータを登録しました。


insert into test (id, value) values (1, '"key1" => "v1_1", "key2" => "v1_2", "key3" => "v1_3"');
insert into test (id, value) values (2, '"key1" => "v2_1", "key2" => "v2_2", "key3" => "v2_3"');





何も考えずselectを実行してみると、一見そのまの文字列で登録されているように見えます。


sample=# select * from test;
id |                     value                    
----+------------------------------------------------
1 | "key1"=>"v1_1", "key2"=>"v1_2", "key3"=>"v1_3"
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
(2 rows)






where句に

[hstoreのフィールド名]->'[キー名]' = '条件'


という指定を行うことで、検索の絞り込みを行うことができます。




sample=# select * from test where value->'key1' = 'v1_1';
id |                     value                    
----+------------------------------------------------
1 | "key1"=>"v1_1", "key2"=>"v1_2", "key3"=>"v1_3"
(1 row)

sample=# select * from test where value->'key1' = 'v2_1';
id |                     value                    
----+------------------------------------------------
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
(1 row)








hstoreに使用できる関数



関数名効果
akeys(hstore)全てのキーをtext型の配列として返す
avals(hstore)全ての値をtext型の配列として返す
skeys(hstore)全てのキーを複数の行にして返す
svals(hstore)全ての値を複数の行にして返す
delete(hstore, keyname)keynameをキーとする値を削除
each(hstore)キーと値を集合として返す



一つずつ効果を見てみます。





akeys




sample=# select id,akeys(value) from test;
id |     akeys    
----+------------------
1 | {key1,key2,key3}
2 | {key1,key2,key3}
(2 rows)



キーのみのフィールドになりました。





avals




sample=# select id,avals(value) from test;
id |     avals    
----+------------------
1 | {v1_1,v1_2,v1_3}
2 | {v2_1,v2_2,v2_3}
(2 rows)



値のみのフィールドになりました。





skeys




sample=# select id,skeys(value) from test;
id | skeys
----+-------
1 | key1
1 | key2
1 | key3
2 | key1
2 | key2
2 | key3
(6 rows)



キーのみ、複数行となりました。






svals




sample=# select id,svals(value) from test;
id | svals
----+-------
1 | v1_1
1 | v1_2
1 | v1_3
2 | v2_1
2 | v2_2
2 | v2_3
(6 rows)



値のみ、複数行となりました。





delete




指定したhstoreフィールドから指定したキーを除外するという意味のようです。


sample=# select id, value from test;
id |                     value                    
----+------------------------------------------------
1 | "key1"=>"v1_1", "key2"=>"v1_2", "key3"=>"v1_3"
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
(2 rows)

sample=# select id, delete(value,'key1') from test;
id |             delete            
----+--------------------------------
1 | "key2"=>"v1_2", "key3"=>"v1_3"
2 | "key2"=>"v2_2", "key3"=>"v2_3"
(2 rows)



こんな感じで、delete(value, 'key1')とすることで、検索結果にkey1が表示されなくなります。

また、arrayを使用することで、複数のキーを指定することも可能です。


sample=# select id, delete(value,array['key1','key2']) from test;
id |     delete    
----+----------------
1 | "key3"=>"v1_3"
2 | "key3"=>"v2_3"
(2 rows)





これを踏まえ、id=1のhstoreからkey3を削除するには、以下のupdate文を実行すればOKです。


sample=# update test set value = delete(value, 'key3') where id = 1;
UPDATE 1
sample=# select * from test order by id;
id |                     value                    
----+------------------------------------------------
1 | "key1"=>"v1_1", "key2"=>"v1_2"
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
(2 rows)







キーが存在するかを条件に検索



上記のupdateを実行したことで、id=1のhstoreからkey3というキーがなくなりました。

hstoreにkey3が存在する列を検索したい場合は、「?」を使用すれば良いようです。


sample=# select * from test where value ? 'key3';
id |                     value                    
----+------------------------------------------------
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
(1 row)











JDBCドライバの対応



JavaからJDBCドライバで接続した時、hstoreがどう見えるか試してみたのですが、
どうも文字列として認識されるようです。
※十分に探しきれていないだけかもしれません。


Hstore PGObject class

こちらを参考に(というかそのままですが)、PGhstoreクラスを作成しました。


  1. package org.postgresql.util;
  3. import java.io.Serializable;
  4. import java.util.Collection;
  5. import java.util.Set;
  6. import java.util.Iterator;
  7. import java.util.LinkedHashMap;
  8. import java.util.Map;
  11. /**
  12.  * Class that handles hstore contrib datatype.
  13.  */
  14. public class PGhstore extends PGobject implements Serializable, Cloneable, Map<Object, Object> {
  16.     private final static long serialVersionUID = 1;
  17.     private Map<Object, Object> hashList;
  19.     public PGhstore() {
  20.         setType("hstore");
  21.     }
  23.     public PGhstore(String value) throws java.sql.SQLException {
  24.         this();
  25.         setValue(value);
  26.     }
  28.     public PGhstore(Map<Object, Object> value) {
  29.         this();
  30.         hashList = new LinkedHashMap<Object, Object>(value);
  31.     }
  33.     @Override
  34.     public void setValue(String s) throws java.sql.SQLException {
  35.         hashList = new LinkedHashMap<Object, Object>();
  36.         
  37.         if (s == null) {
  38.             return;
  39.         }
  40.         
  41.         char[] chars = s.toCharArray();
  42.         String key = null;
  43.         StringBuffer buffer = new StringBuffer();
  44.         boolean insideKey = true;
  45.         boolean insideVal = false;
  46.         boolean insideString = false;
  48.         for (int i = 0; i < chars.length; i++) {
  49.             // escape character that we need to skip
  50.             if (chars[i] == '\\') {
  51.                 i++;
  52.             }
  54.             // white space
  55.             else if (!insideString && Character.isWhitespace(chars[i])) {
  56.                 continue;
  57.             }
  59.             // the => between key and value
  60.             else if (!insideString && chars[i] == '=') {
  61.                 i++;
  62.                 if (i == chars.length)
  63.                     throw new PSQLException("Unexpected end of string",
  64.                             PSQLState.DATA_ERROR);
  66.                 if (!insideKey || chars[i] != '>')
  67.                     throw new PSQLException(
  68.                             "Syntax error at position " + i,
  69.                             PSQLState.DATA_ERROR);
  71.                 insideKey = false;
  72.                 insideVal = true;
  74.                 key = buffer.toString();
  75.                 buffer.setLength(0);
  77.                 continue;
  78.             }
  80.             // quote, item separator or end of string
  81.             else if (chars[i] == '"' || (!insideString && chars[i] == ',')
  82.                     || i == chars.length - 1) {
  83.                 if (chars[i] == '"') {
  84.                     insideString = !insideString;
  85.                     if (i != chars.length - 1)
  86.                         continue;
  87.                 } else if (chars[i] != ',' && buffer != null) {
  88.                     buffer.append(chars[i]);
  89.                 }
  91.                 String b = (buffer == null) ? null : buffer.toString();
  93.                 // end of element, add it to list
  94.                 if (b != null && (b.length() > 0 || insideVal)) {
  95.                     hashList.put(key, b.equalsIgnoreCase("NULL") ? null : b);
  96.                 }
  98.                 insideKey = true;
  99.                 insideVal = false;
  100.                 buffer = new StringBuffer();
  102.                 continue;
  103.             }
  105.             if (buffer != null) {
  106.                 buffer.append(chars[i]);
  107.             }
  108.         }
  109.     }
  111.     @Override
  112.     public String getValue() {
  113.         if (hashList == null) {
  114.             return null;
  115.         }
  117.         Iterator<Entry<Object, Object>> iter = hashList.entrySet().iterator();
  118.         if (!iter.hasNext()) {
  119.             return null;
  120.         }
  122.         Entry<Object, Object> e = iter.next();
  123.         StringBuffer buffer = new StringBuffer();
  124.         appendEntry(buffer, e);
  126.         while (iter.hasNext()) {
  127.             e = iter.next();
  128.             buffer.append(',');
  129.             appendEntry(buffer, e);
  130.         }
  132.         return buffer.toString();
  133.     }
  135.     private void appendEntry(StringBuffer buf, Entry<Object, Object> e) {
  136.         appendValue(buf, e.getKey(), true);
  137.         buf.append("=>");
  138.         appendValue(buf, e.getValue(), false);
  139.     }
  141.     private void appendValue(StringBuffer buf, Object v, boolean isKey) {
  142.         if (v == null) {
  143.             if (isKey)
  144.                 buf.append("\"NULL\"");
  145.             else
  146.                 buf.append("NULL");
  147.             return;
  148.         }
  150.         String s = v.toString();
  152.         buf.append('"');
  153.         for (int i = 0; i < s.length(); i++) {
  154.             char c = s.charAt(i);
  155.             if (c == '"' || c == '\\')
  156.                 buf.append('\\');
  157.             buf.append(c);
  158.         }
  159.         buf.append('"');
  160.     }
  162.     public Collection<Object> values() {
  163.         return hashList.values();
  164.     }
  166.     public int size() {
  167.         return hashList.size();
  168.     }
  170.     public Object remove(Object key) {
  171.         return hashList.remove(key);
  172.     }
  174.     public void putAll(Map<? extends Object,? extends Object> m) {
  175.         hashList.putAll(m);
  176.     }
  178.     public Object put(Object key, Object value) {
  179.         return hashList.put(key, value);
  180.     }
  182.     public Set<Object> keySet() {
  183.         return hashList.keySet();
  184.     }
  186.     public boolean isEmpty() {
  187.         return hashList.isEmpty();
  188.     }
  190.     public Set<Entry<Object, Object>> entrySet() {
  191.         return hashList.entrySet();
  192.     }
  194.     public boolean containsKey(Object key) {
  195.         return hashList.containsKey(key);
  196.     }
  198.     public Object get(Object key) {
  199.         return hashList.get(key);
  200.     }
  202.     public boolean containsValue(Object value) {
  203.         return hashList.containsValue(value);
  204.     }
  206.     public void clear() {
  207.         hashList.clear();
  208.     }
  210. }





こんなプログラムにすることで、hstoreをmapオブジェクトとして取り出すことができます。


  1. package com.fc2.blog68.symfoware.pg;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.Statement;
  8. import org.postgresql.util.PGhstore;
  10. public class Sample {
  12.     public static void main(String[] args) {
  13.         try {
  14.             String url = "jdbc:postgresql://192.168.1.12:5432/sample";
  15.             String user = "pguser";
  16.             String pass = "pguser";
  18.             Connection con = DriverManager.getConnection(url, user, pass);
  19.             
  20.             Statement stmt = con.createStatement();
  21.             
  22.             ResultSet rs = stmt.executeQuery("select * from test order by id");
  23.             
  24.             while(rs.next()) {
  25.                 System.out.print(rs.getString("id"));
  26.                 System.out.print("\t");
  27.                 PGhstore map = new PGhstore(rs.getString("value"));
  28.                 System.out.println(map.get("key1"));
  29.             }
  30.             
  31.             stmt.close();
  32.             con.close();
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         }
  37.     }
  38. }




実行結果


1    v1_1
2    v2_1






データの追加はこんな感じでしょうか。


  1. package com.fc2.blog68.symfoware.pg;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.Statement;
  6. import java.util.HashMap;
  7. import java.util.Map;
  9. import org.postgresql.util.PGhstore;
  11. public class Sample {
  13.     public static void main(String[] args) {
  14.         try {
  15.             String url = "jdbc:postgresql://192.168.1.12:5432/sample";
  16.             String user = "pguser";
  17.             String pass = "pguser";
  19.             Connection con = DriverManager.getConnection(url, user, pass);
  20.             
  21.             Statement stmt = con.createStatement();
  22.             
  23.             Map<Object, Object> javaMap = new HashMap<Object, Object>();
  24.             javaMap.put("key1", "v3_1");
  25.             javaMap.put("key2", "v3_2");
  26.             javaMap.put("key3", "v3_3");
  27.             
  28.             PGhstore map = new PGhstore(javaMap);
  29.             
  30.             stmt.execute("insert into test (id, value) values (3, '" + map.getValue() + "')");
  31.             
  32.             stmt.close();
  33.             con.close();
  35.         } catch (Exception e) {
  36.             e.printStackTrace();
  37.         }
  38.     }
  39. }




ちゃんと登録されたようです。


sample=# select * from test order by id;
id |                     value                    
----+------------------------------------------------
1 | "key1"=>"v1_1", "key2"=>"v1_2"
2 | "key1"=>"v2_1", "key2"=>"v2_2", "key3"=>"v2_3"
3 | "key1"=>"v3_1", "key2"=>"v3_2", "key3"=>"v3_3"









【参考URL】

ログ解析にhstoreを利用してみよう

PostgreSQL hstoreでKVS

F.16. hstore

Hstore PGObject class

Patch: Add support for hstore extension - and map it to/from java.util.Map



関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/16(水) 23:29:45|
  2. 備忘録
  3. | トラックバック:0
  4. | コメント:0

MySQLとPostgreSQLのコマンド対応

すぐ忘れるのでメモしておきます。


やりたいことmysqlpostgresql
コマンドラインツール起動mysql -u[ユーザー] -p[パスワード](#su - postgresの後) psql
データベースの一覧表示show database\l
データベース切り替えuse [データベース名]\c [データベース名]
テーブル一覧表示show tables\d
ツールの終了exit\q

関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/16(水) 21:14:34|
  2. 備忘録
  3. | トラックバック:0
  4. | コメント:0

Arch Linux + Postgresqlで障害管理ツールMantisを動かす

Arch LinuxにPostgresqlをインストール。
nginx + phpで接続できるようになりました。

Arch LinuxにPostgresqlをインストールする
Arch Linux + nginx + php-fpm + Postgresqlな環境を作成する

せっかくなので、障害管理ツールのMantisを動かしてみます。
普段はMySQLで使用しているので、Postgresqlで動かすのは初めてです。






データベースの作成



データベースの文字コードを明示的に指定したかったので、Mantisで使用するデータベースを事前に作成しておきます。

rootでログインしている場合には、postgresユーザーになり、
createdbコマンドを実行して、データベースを作成します。


[root@(none) html]# su - postgres
[postgres@(none) ~]$ createdb -E UTF8 -T template0 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8 mantis





/etc/nginx/htmlに移動して、mantisbt-1.2.10.tar.gzをダウンロード。
ファイルを解凍して、mantisというフォルダにリネーム。
nginxのユーザーhttpにアクセス権を付与します。


# cd /etc/nginx/html
# wget -O mantisbt-1.2.10.tar.gz http://sourceforge.net/projects/mantisbt/files/mantis-stable/1.2.10/mantisbt-1.2.10.tar.gz/download
# tar xzf mantisbt-1.2.10.tar.gz
# mv mantisbt-1.2.10 mantis
# chown -R http:http ./mantis







http://[サーバーのIP]/mantis/にアクセスすると、データベース設定の画面が表示されます。
Type of DatabaseにPostgreSQLを選択。
PostgreSQLに接続するユーザー名とパスワードを入力し、Database nameに
先程作成したデータベースの名称を指定します。

63_001.png


下のInstallを押すと、さくっとインストール出来ました。


軽くプロジェクトの作成や案件の登録を行ってみたところ、どうも添付ファイルの
扱いに問題があるようで、添付したファイルが壊れてしまいました。

63_002.png



これに対応するため、保存先をデータベースからディスクに変更してみます。

mantisフォルダにあるconfig_inc.phpを編集。
$g_file_upload_method = DISK;という記載を追加します。


<?php
         $g_hostname = 'localhost';
    $g_db_type = 'pgsql';
    $g_database_name = 'mantis';
    $g_db_username = 'pguser';
    $g_db_password = 'pguser';

    $g_default_language = "japanese";
    $g_file_upload_method = DISK;
?>




アップロードしたファイルを保存するため、適当なフォルダを作成します。
今回は、/etc/nginx/html/mantis/filesというフォルダを作成しました。

httpユーザーにアクセス権を付与します。

# mkdir /etc/nginx/html/mantis/files
# chown -R http:http /etc/nginx/html/mantis/files




mantisの[システム管理]-[プロジェクト管理]からプロジェクト名を選択すると、
[ファイルの登録場所]という項目が追加されているので、ここに先程作成したフォルダ名を指定し、
更新します。

63_003.png



これでファイルのアップロードが行えるようになりました。

63_004.png






【参考URL】

ファイルアップロード






関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/07(月) 23:06:06|
  2. 備忘録
  3. | トラックバック:1
  4. | コメント:0

Arch Linux + nginx + php-fpm + Postgresqlな環境を作成する

Arch LinuxにPostgresqlをインストールしてみました。
Arch LinuxにPostgresqlをインストールする


このデータベースにphpから接続してみたいと思います。





nginx + php-fpm



以前、nginx + php-fpm + MySQLの環境を構築してみたことがあります。
Arch Linuxにnginx + php-fpm + mysqlの環境を作成する

これを参考に、nginx + php-fpmの環境を作成します。





pacmanでnginx、php-fpm、php-pgsqlをインストール。


# pacman -S nginx php-fpm php-pgsql





次に、php.iniを編集します。


# vi /etc/php/php.ini





編集するのは、
open_basedirに/etc/nginx/html/を追加。
extensionのpdo_pgsql.soとpgsql.soのコメントをはずし、有効にする。
という2箇所です。


#/etc/nginx/html/を追加
#open_basedir = /srv/http/:/home/:/tmp/:/usr/share/pear/
open_basedir = /etc/nginx/html/:/srv/http/:/home/:/tmp/:/usr/share/pear/


#コメントをはずして有効化
extension=pdo_pgsql.so
extension=pgsql.so





編集がおわったら、php-fpmを起動しておきます。


# rc.d start php-fpm






次は、nginx.confの編集です。


# vi /etc/nginx/conf/nginx.conf





編集箇所の抜粋は以下の通り。


    server {
        listen     80;
        server_name localhost;

        #charset koi8-r;

        #access_log logs/host.access.log main;

        location / {
            root html;
            index index.html index.htm index.php;
        }



        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        location ~ \.php$ {
            try_files     $uri =404;
            root         html;
            fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;
            fastcgi_index index.php;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            include        fastcgi_params;
        }
    }






nginx.confの編集が終わったら、nginxを起動します。


# rc.d start nginx





phpinfoを表示させて動作確認します。


# vi /etc/nginx/html/info.php





内容はこんな感じ。


  1. <? phpinfo(); ?>



http://[サーバーIP]/info.phpにブラウザでアクセスするとphpinfoが表示されます。
pdo_pgsqlとpgsqlがちゃんと読み込まれてます。

62_001.png




簡単な接続サンプルを書いて、phpからPostgresqlに接続できるか試して見ました。


  1. <html>
  2. <head><title>PHP TEST</title></head>
  3. <body>
  5. <?php
  7. $conn = "host=localhost dbname=test user=pguser password=pwd";
  8. $link = pg_connect($conn);
  9. if (!$link) {
  10.     die(pg_last_error());
  11. }
  13. print('Connect OK!<br>');
  15. pg_close($link);
  17. ?>
  18. </body>
  19. </html>





ちゃんと接続できているようです。

62_002.png






【参考URL】

NginxでのCakePHPサイト設置

PostgreSQLへ接続




関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/07(月) 22:01:04|
  2. 備忘録
  3. | トラックバック:1
  4. | コメント:0

Postgresql 9.1のCOPYでファイルの文字コードを指定する

地味にハマったのでメモ。


PostgresqlでCSVファイルをテーブルに取り込むには、COPYを使うのが便利だとわかりました。
Postgresql 9.1でCOPYを使用し、csvファイルからデータをインポートする


今回、お題として日本郵便で公開されている郵便番号データを
プログラムを使用せず、Postgresqlのテーブルに取り込んでみようと思います。
ただ、この郵便番号ファイルはShift-JISで、データベースはUTF8なんですよ。




unzipのインストール



最近知ったのですが、郵便番号データはlzhだけでなくzipでも公開されています。
今回は、Arch LinuxにインストールしたPostgresqlで試してみるので、
pacmanでunzipをインストールしておきました。


# pacman -S unzip








郵便番号データのダウンロードと展開



zip形式の郵便番号データはこちらで公開されています。
http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

/tmpにダウンロードして、展開しておきます。
なお、ここからの作業はpostgresユーザーで行なっています。


$ cd /tmp
$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
$ unzip ken_all.zip



これで、/tmp/KEN_ALL.CSVというファイルができたかと思います。






作戦



ダウンロードしたファイルにはいろいろな情報が含まれています。
また、都道府県や市町村名、番地が別々のフィールドに入っています。

ファイルの形式は以下を参照。
http://www.post.japanpost.jp/zipcode/dl/readme.html


ここで考えた作戦が一旦テンポラリテーブルにCSVファイルをそのまま取り込み。
その後、ほしい形にSQLで整形しつつ一括インサート。

まず、CSVのレイアウトと同じ形式のテンポラリテーブルを作成します。


CREATE LOCAL TEMPORARY TABLE post_tmp (
全国地方公共団体コード int,
旧郵便番号 char(5),
郵便番号 char(7),
都道府県名カナ varchar(20),
市区町村名カナ varchar(100),
町域名カナ varchar(100),
都道府県名 varchar(20),
市区町村名 varchar(100),
町域名 varchar(100),
一町域が二以上の郵便番号 int,
小字毎に番地が起番 int,
丁目を有する町域 int,
一つの郵便番号で二以上の町域 int,
更新の表示 int,
変更理由 int
);



日本語名フィールドがそのまま通るので、あまり悩まずわかりやすいフィールド名が付けられますね。


次にこのテンポラリテーブルにcsvファイルからcopy。


COPY post_tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' CSV;



encodingオプションで'SJIS'をしていしてやります。
ポイントはCSVを指定するのですが、「FORMAT CSV」としないこと。

こんなエラーになります。

# COPY post_tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' FORMAT CSV;
ERROR: syntax error at or near "FORMAT"
LINE 1: ..._tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' FORMAT CSV...





これで、郵便番号の生データをPostgresqlに取り込むことが出来ました。


最終的にほしいテーブルの形式はこちら。

CREATE TABLE post (
郵便番号 char(7),
住所カナ varchar(500),
住所 varchar(500)
);



「郵便番号」
都道府県名カナと市区町村名カナと町域名カナを結合した「住所カナ」
都道府県名と市区町村名と町域名を結合した「住所」

post_tmpにいい感じでselectし、結果をそのままpostにinsertしてやります。


INSERT INTO post SELECT 郵便番号,都道府県名カナ || 市区町村名カナ || 町域名カナ, 都道府県名 || 市区町村名 || 町域名 FROM post_tmp;





Javaで適当にサンプルを書いて実行してみます。


  2. package com.fc2.blog68.symfoware.pg;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.Statement;
  9. public class Sample {
  11.     public static void main(String[] args) {
  12.         try {
  13.             String url = "jdbc:postgresql://192.168.1.12:5432/test";
  14.             String user = "pguser";
  15.             String pass = "password";
  17.             Connection con = DriverManager.getConnection(url, user, pass);
  18.             
  19.             Statement stmt = con.createStatement();
  20.             
  21.             ResultSet rs = stmt.executeQuery("select * from post where 住所 like '%銀座%'");
  22.             
  23.             while(rs.next()) {
  24.                 System.out.print(rs.getString("郵便番号"));
  25.                 System.out.print("\t");
  26.                 System.out.print(rs.getString("住所カナ"));
  27.                 System.out.print("\t");
  28.                 System.out.println(rs.getString("住所"));
  29.             }
  30.             
  31.             stmt.close();
  32.             con.close();
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         }
  37.     }
  38. }





実行結果は狙い通り。


0691331    ﾎｯｶｲﾄﾞｳﾕｳﾊﾞﾘｸﾞﾝﾅｶﾞﾇﾏﾁｮｳｷﾞﾝｻﾞ    北海道夕張郡長沼町銀座
3220052    ﾄﾁｷﾞｹﾝｶﾇﾏｼｷﾞﾝｻﾞ    栃木県鹿沼市銀座
3600032    ｻｲﾀﾏｹﾝｸﾏｶﾞﾔｼｷﾞﾝｻﾞ    埼玉県熊谷市銀座
3670052    ｻｲﾀﾏｹﾝﾎﾝｼﾞｮｳｼｷﾞﾝｻﾞ    埼玉県本庄市銀座
1040061    ﾄｳｷｮｳﾄﾁｭｳｵｳｸｷﾞﾝｻﾞ    東京都中央区銀座
9300991    ﾄﾔﾏｹﾝﾄﾔﾏｼｼﾝｼﾞｮｳｷﾞﾝｻﾞ    富山県富山市新庄銀座
3940022    ﾅｶﾞﾉｹﾝｵｶﾔｼｷﾞﾝｻﾞ    長野県岡谷市銀座
3940023    ﾅｶﾞﾉｹﾝｵｶﾔｼﾋｶﾞｼｷﾞﾝｻﾞ    長野県岡谷市東銀座
3950031    ﾅｶﾞﾉｹﾝｲｲﾀﾞｼｷﾞﾝｻﾞ    長野県飯田市銀座
4240817    ｼｽﾞｵｶｹﾝｼｽﾞｵｶｼｼﾐｽﾞｸｷﾞﾝｻﾞ    静岡県静岡市清水区銀座
4130013    ｼｽﾞｵｶｹﾝｱﾀﾐｼｷﾞﾝｻﾞﾁｮｳ    静岡県熱海市銀座町
4140028    ｼｽﾞｵｶｹﾝｲﾄｳｼｷﾞﾝｻﾞﾓﾄﾏﾁ    静岡県伊東市銀座元町
4750874    ｱｲﾁｹﾝﾊﾝﾀﾞｼｷﾞﾝｻﾞﾎﾝﾏﾁ    愛知県半田市銀座本町
4480845    ｱｲﾁｹﾝｶﾘﾔｼｷﾞﾝｻﾞ    愛知県刈谷市銀座
5220088    ｼｶﾞｹﾝﾋｺﾈｼｷﾞﾝｻﾞﾁｮｳ    滋賀県彦根市銀座町
6128089    ｷｮｳﾄﾌｷｮｳﾄｼﾌｼﾐｸｷﾞﾝｻﾞﾁｮｳ    京都府京都市伏見区銀座町
7450032    ﾔﾏｸﾞﾁｹﾝｼｭｳﾅﾝｼｷﾞﾝｻﾞ    山口県周南市銀座
7450033    ﾔﾏｸﾞﾁｹﾝｼｭｳﾅﾝｼﾐﾅﾐｷﾞﾝｻﾞ    山口県周南市みなみ銀座
7700916    ﾄｸｼﾏｹﾝﾄｸｼﾏｼｷﾞﾝｻﾞ    徳島県徳島市銀座
8040076    ﾌｸｵｶｹﾝｷﾀｷｭｳｼｭｳｼﾄﾊﾞﾀｸｷﾞﾝｻﾞ    福岡県北九州市戸畑区銀座












【参考URL】

PostgreSQL CSVファイルからのインポート

http://www.postgresql.jp/document/current/html/sql-copy.html

PostgreSQL の COPY コマンドと SQL だけで様々なデータをインポートする

関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/06(日) 19:41:18|
  2. 備忘録
  3. | トラックバック:1
  4. | コメント:0
次のページ