Memo/PostgreSQL

http://dexlab.net/pukiwiki/index.php?Memo%2FPostgreSQL
 

Memo/PostgreSQL


アップグレード


MySQLとのコマンド比較


クエリ結果を見やすく表示

  • '\x': MySQLの'\G'相当。
    1. test=# \x 拡張表示は on です test=# select * from users; test=# select * from users; -[ RECORD 1 ] id   | 1 name | satou -[ RECORD 2 ] id   | 2 name | suzuki

テーブルやカラムにコメントを付ける

  • COMMENT
    1. createdb -U postgres -E utf8 test
    2. psql -U postgres test
    3. # \encoding utf8
    4. # create table users (id int);
    5. # COMMENT ON TABLE  users IS 'ユーザ';
    6. # COMMENT ON COLUMN users.id IS 'ユーザID';
  • psqlコマンドで一覧を見るとき
    1. test=# \encoding utf8
    2.  
    3. test=# \d+                                                                                                                             
    4.                     リレーションの一覧
    5.  スキーマ | 名前  |    型    |  所有者  | サイズ  |  説明  
    6. ----------+-------+----------+----------+---------+--------
    7.  public   | users | テーブル | postgres | 0 bytes | ユーザ
    8. (1 行)
    9.  
    10. test=# \d+ users                                                                                                                              
    11.               テーブル "public.users"
    12.  カラム |   型    | 修飾語 | ストレージ |   説明  
    13. --------+---------+--------+------------+----------
    14.  id     | integer |        | plain      | ユーザID
    15. OID を持つ: no

PostGIS


PostgreSQL9.x をCentOSにインストール

  • 環境
    • CentOS 6.3 x86_64
    • PostgreSQL 9.1
  1. OSに合わせてリポジトリを選択 http://yum.postgresql.org/repopackages.php
  2. インストール
    1. rpm -ivh http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
    2. yum install postgresql91 --enablerepo=pgdg91
    3. ldconfig

カンマ区切りのカラムから分割して取得

IDnumebr
11,2,3,4,5
26,7,8,9,10
  • PostgreSQL 8.3以降で regexp_split_to_table() 関数が使える
    1. SELECT id,regexp_split_to_table(number, ',') AS number FROM hoge;
    2. ---------------------------
    3. ID,number
    4. 1 1 
    5. 1 2 
    6. 1 3 
    7. 1 4 
    8. 1 5 
    9. 2 6 
    10. 2 7 
    11. 2 8 
    12. 2 9 
    13. 2 10 
    14. ---------------------------

PostgreSQL9.0をrpmからインストール

  • CentOS5.6
    1. # PostgreSQL 9.0用リポジトリ
    2. rpm -Uvh http://yum.pgsqlrpms.org/9.0/pgdg-centos-9.0-2.noarch.rpm
    3. perl -p -i -e 's#enabled\s*=\s*1#enabled=0#' "/etc/yum.repos.d/pgdg-90-centos.repo"
    4.  
    5.  
    6. # PostgreSQL9.0でapr依存でエラーがでるので、compat-postgresql-libsをインストール
    7. ARCH=`uname -i`
    8. local arch="i686"
    9. if [ "$ARCH" == "" ]; then
    10. $arch=$ARCH
    11. fi
    12. wget -q http://www.pgrpms.org/9.0/redhat/rhel-5.0-$ARCH/compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
    13. rpm -Uvh --force compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
    14. rm -f compat-postgresql-libs-4-1PGDG.rhel5.$arch.rpm
    15.  
    16. yum -y install postgresql postgresql-server postgresql-contrib --enablerepo=pgdg90
    17.  
    18. su - postgres -c 'initdb --no-locale --encoding=UTF8'
    19.  
    20. service postgresql-9.0 restart
    21. chkconfig postgresql-9.0 on
  • bin
    • /usr/pgsql-9.0/bin/
  • data
    • /var/lib/pgsql/9.0/data/

textsearch_sennaで全文検索


9.1でレプリケーション


9.0でレプリケーション


データベースサイズの確認

  1. SELECT pg_database_size('データベース名')/1024/1024 || 'MB';

チューニング/最適化


pgbench

  • pgbench 8.1.18-2.el5_4.1
    1. yum install postgresql-server postgresql-contrib --disablerepo=pgdg83
    2. service postgresql start
    3. su - postgres
    4. createdb pgbench
    5. pgbench -i pgbench
    6.  
    7. 100クライアント(-c)100トランザクション(-t)を実行した場合のベンチマーク
    8. pgbench -c 100 -t 100 pgbench
    9.  
    10. dropdb pgbench

Senna + Ludiaで全文検索


カンマ区切りで取得

  • 単純な例
    select array_to_string(ARRAY(select t from tbl1), ',');
  • 関連テーブルを挟んだ場合
    select
      users.id
      , (
          select
            array_to_string(
              ARRAY(
                select
                  groups.name
                from
                  groups
                  , users_groups
                where
                  groups.id = users_groups.compgroup_id
                  and users.id = users_groups.company_id
                   ),
            ',')
        ) as name
    from
      users
    

階層問い合わせ

ツリー構造で欲しい場合等。connectby()関数

$ su
# yum install postgresql-contrib
# exit
$ psql -U postgres doms -f /usr/share/pgsql/contrib/tablefunc.sql
$ psql -U postgres doms_dev -f /usr/share/pgsql/contrib/tablefunc.sql
$ psql -U postgres doms_tests -f /usr/share/pgsql/contrib/tablefunc.sql

シーケンスを最大値で更新

SELECT setval('シーケンス名', max(id)) FROM テーブル名;

正規表現

  • 「type=数字」で数字部分を取り出す。「#」はエスケープに使う文字。なぜか\dとかは使えない
    select substring( var from 'type=([0-9]+)' for '#') as num from hoge;
  • 条件に使う
    select * from hoge where var ~ 'type=(\\d+)'

月末の取得

  • 月末
    SELECT to_char(to_date('20050101', 'YYYYMMDD') + interval '1 month' - interval '1 day', 'YYYYMMDD');

バージョンの取得

  • SQLでバージョン取得
    =# select version();
                                                                 version                                                              
    ----------------------------------------------------------------------------------------------------------------------------------
     PostgreSQL 7.3.4-RH on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1)
    (1 row)
    ----
  • バージョン番号だけ取得
    =# select substring(version() from 'PostgreSQL[ ]+([0-9]+\.[0-9]\.[0-9])');
     substring 
    -----------
     7.3.4
    (1 row)
    
  • psqlのバージョン
    $ psql --version
    psql (PostgreSQL) 7.3.4-RH
    contains support for command-line editing
    

invalid multibyte character for locale HINT:

  • psql (PostgreSQL) 8.1.11 でエラー
    Notice: Tried 'SELECT * FROM articles WHERE LOWER(articles.title) = ? LIMIT 1'. Got: [-1] ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database encoding. in /var/www/akelos/lib/AkActiveRecord/AkDbAdapter.php on line 226
  • 030 WEB DESIGN: PostgreSQLアーカイブ どうやら、initdbの時の文字コードが問題の模様
    # pg_controldata /var/lib/pgsql/data/
    
    LC_COLLATE:                           ja_JP.eucjp
    LC_CTYPE:                             ja_JP.eucjp
    
    # service postgresql stop
    # su - postgres
    $ rm /var/lib/pgsql/data
    $ initdb --locale=C /var/lib/pgsql/data
    $ exit
    # service postgresql start
    # pg_controldata /var/lib/pgsql/data/
    
    LC_COLLATE:                           C
    LC_CTYPE:                             C
  • しあわせ太郎のCGI光房 postgresql.confを変更して、再起動でも良いらしい
    • /var/lib/pgsql/data/postgresql.conf
      lc_messages = 'C'                       # locale for system error message 
                                              # strings
      lc_monetary = 'C'                       # locale for monetary formatting
      lc_numeric = 'C'                        # locale for number formatting
      lc_time = 'C'                           # locale for time formatting
      

sha1

  • PostgreSQL 8.0以降なら、md5は select md5('〜'); で取得できる。
  • postgresql-python, postgresql-perl 等が必要
  • plpythonu (8.0以降はUntrustedな言語なのでuが付く)
    CREATE LANGUAGE plpythonu;
    CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS '
    import sha
    hash = sha.new( args[0] )
    return hash.hexdigest()
    ' LANGUAGE plpythonu;
  • plperl
    CREATE LANGUAGE plperl;
    CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS '
    use Digest::SHA1 qw(sha1);
    return sha1($args[0]);
    ' LANGUAGE plperl;
    

日付と時刻型の比較

created_atがtimestamp型で、指定日以下の行が欲しい場合。
castするか、秒まで指定しないと当日分は抽出されない。

  • 取得できない
    select * from authors where created_at <= '2008-07-04';
  • 取得できる
    select * from authors where created_at <= '2008-07-04 23:59:59';
    select * from authors where cast(created_at as date) <= '2008-07-04';

あいまい検索

  • 半角から全角へ変換。
  • 前方一致の場合インデックスを作成するとさらに高速に
    • CREATE INDEX test_tbl_textdata ON test_tbl (get_nocase_text(textdata));
    • SELECT * FROM test_tbl WHERE get_nocase_text(textdata) LIKE get_nocase_text('A4') || '%';

月で絞り込み

  • 月と同じ
    "SELECT * FROM table_A WHERE date_trunc('month', ins_date) = to_date('".$year."-".$month."-01"', 'YYYY-MM-DD')"
  • 月以下
    "SELECT * FROM table_A WHERE date_trunc('month', ins_date) <= to_date('".$year."-".$month."-01"', 'YYYY-MM-DD')"

秒を日付に変換

  • int型の項目に秒が入っていて、出力する時に日付(HH:MI:SS)とかにしたいとき。PostgreSQL 7.4.x
    select colum1 * INTERVAL '1 second' from table1;
    ----
    00:01:00
    ----
  • epoch関数がPostgreSQL 8.x で追加
    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

時間間隔型

  • 1秒、1日、1年等の時間間隔を格納できる。
  • 格納した値で、日付計算が可能
CREATE TABLE time_limit
(
	time interval
);

INSERT INTO time_limit VALUES ('0 day');
INSERT INTO time_limit VALUES ('1 day');
INSERT INTO time_limit VALUES ('30 days');
INSERT INTO time_limit VALUES ('1 year');
INSERT INTO time_limit VALUES ('10 years');

select CURRENT_TIMESTAMP + time from time_limit;
ptmp=> select CURRENT_TIMESTAMP + time from time_limit;
           ?column?            
-------------------------------
 2007-07-12 15:54:54.308539+09
 2007-07-13 15:54:54.308539+09
 2007-08-11 15:54:54.308539+09
 2008-07-12 15:54:54.308539+09
 2017-07-12 15:54:54.308539+09
(5 rows)

numericを'HH:MI:SS'に変換する

  • numeric -> intervalが使えれば良いのだがうまくいかなかったので力ずく。FMを付けないと空白が強制的に出力される
    select
    	to_char(sec/3600,'FM00')
    	|| ':' || to_char(sec%3600/60,'FM00')
    	|| ':' || to_char(sec%60,'FM00')
    from
    	(select 45296 as sec) tmp
    • 結果
      ?column? 
      ----------
      12:34:56
      (1 row)
  • もっと簡単にできないものか。
    • OK interval '1 second'
    • NG interval sec || ' second'

クエリーをcsv出力

  • psqlから実行する場合
    $ psql dbname
    dbname=# \o tablenams.csv
    dbname=# \a
    Output format is unaligned.
    dbname=# \pset fieldsep ','
    Field separator is ','.
    dbname=# select * from tablename;
    dbname=# \o
    • \o で出力先を指定
    • \a で桁そろえをしない
    • \pset fieldsep でセパレータを指定
    • '\t' ならタブ区切り
    • select 文で対象データを抽出
    • 最後の、\o で出力先を戻します。
  • コマンドライン
    • -A 桁そろえをしない
    • -F セパレータを指定
      $ psql -c 'SELECT * FROM tablename;' -A -F,

圧縮ダンプ/リストア

  • ダンプ
    pg_dump dbname | gzip > filename.gz
  • 全データベース
    pg_dumpall -U postgres | gzip > postgresql.YYYYMMDD.gz
  • リストア
    cat filename.gz | gunzip | psql dbname
  • 異なるオーナーのDBにインポート場合
    pg_dump -O 元DB名 | gzip > filename.gz
    cat filename.gz | gunzip | psql -U 対象のユーザー名 新DB名

カラムのデフォルト値変更

alter table テーブル名 alter カラム名 set default 'デフォルト値';
alter table テーブル名 alter カラム名 drop default;

ラージオブジェクトの情報を取得する

SELECT
    loid as "ID",
    pg_catalog.obj_description(loid, 'pg_largeobject') as  "Description"
FROM
    (SELECT DISTINCT
        loid
    FROM
        pg_catalog.pg_largeobject
    ) x
ORDER BY
    "ID"

カラム情報を取得する

SELECT
    attname,
    format_type(atttypid, atttypmod) as format_type,
    col_description(attrelid, attnum) as col_description
FROM
    pg_attribute,
    pg_class c
WHERE
    c.oid = attrelid AND
    attnum > 0 AND
    c.relname = 'tablename' ;

ログをsyslogに出力する

  1. postgresql.confの編集
    $ su - postgres
    $ vi data/data/postgresql.conf
    ----------------------------------
    syslog = 2                      # range 0-2; 0=stdout; 1=both; 2=syslog
    syslog_facility = 'LOCAL0'
    syslog_ident = 'postgres'
    ----------------------------------
  2. syslog.confの編集。/var/log/messagesには出力しないようにする
    # vi /etc/syslog.conf
    ----------------------------------
    *.info;mail.none;authpriv.none;cron.none;local0.none    /var/log/messages
    local0.*    /var/log/postgresql.log 
    ----------------------------------
    # service syslog restart
  3. logrotateに追加
    # vi /etc/logrotate.d/syslog
    1行目の行末に追加
    ----------------------------------
    /var/log/postgresql.log
    (
    ...
    )
    ----------------------------------
  4. 再起動
    # service syslog restart
    # service postgres restart
  5. ログは
    /var/log/message

パスワードファイル

  • $HOME/.pgpass ファイルにパスワードを記述する事で、vacuum等をcronで処理したい時などにパスワードを毎回入力する必要がなくなります。
    $ vi ~/.pgpass
    ----------------------
    $ 0600 ~/.pgpass
    $ hostname:port:database:username:password
    *:*:*:postgres:postgresユーザのパスワード
    *:*:*:apache:apacheユーザのパスワード
    ----------------------
    $ chmod 600 ~/.pgpass

VACUUM

  • データベースの不要領域の回収、およびオプションによるデータベースの解析
  • 毎日4:25に全てのDBに対し、バキュームする場合
    25 4 * * * (/usr/bin/vacuumdb -a -z -U postgres) > /dev/null 2>&1
  • 毎日4:25に全てのDBに対し、完全バキュームする場合
    (-fオプションだとその間ロックされるため、更新できなくなる)
    25 4 * * * (/usr/bin/vacuumdb -a -z -f -U postgres) > /dev/null 2>&1

条件を指定して一括Updateする

PostgreSQLでは、Update文にFrom句が指定できるので、柔軟な更新が可能です。
(OracleやMySQLでは使えません)

  • 例:testtblのnameを4桁固定。'0'埋めにする
    update testtbl set name = ('0' || testtbl.name)
    from
    	(select * from testtbl where char_length(name) < 4) tmp
    where 
    	testtbl.code = tmp.code

pgpoolで可用性を上げる

スクリプトをUTF-8で書く場合の注意点

sqlスクリプトファイルをUTF-8で保存する際に、BOM付きで保存すると、インポートの際にエラーが出ます。(PostgreSQL 7.4.7確認)
特に害は無いようですが、気になるようならば、BOM無し(UTF-8N)で保存すると良いようです。

$ psql -U apache -h localhost scott
scott=> \encoding UTF-8
scott=> \i import.sql

psqlのログを出力する

  • windowsの場合
    (psql -h localhost [DB名] < 入力ファイル.log) 2>&1) > psql.log

参照制約の取得

SELECT
	conname,
	pg_catalog.pg_get_constraintdef(oid) as condef
FROM
	pg_catalog.pg_constraint r
WHERE
	r.contype = 'f'

シーケンス一覧の取得

SELECT
 c.relname as "Name"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
     AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

シーケンスの値をテーブルの値で更新する

テーブルの値を用いて、シーケンスの値を更新する時は、bigintにcastする必要がある。

  • 例:deptテーブルのdeptnoの最大値をシーケンスに設定する場合。
    × select setval('sample_seq', max(deptno)) from dept;
    ○ select setval('sample_seq', cast(max(deptno) as bigint)) from dept;
    ○ select setval('sample_seq', (select cast(max(deptno) as bigint) from dept));
  • 確認環境
    • psql (PostgreSQL) 7.3.9-RH
    • psql (PostgreSQL) 7.4

シーケンス関数

select nextval('sample_seq');次の値の取得
select currval('sample_seq');現在値の取得。nextval実行後のみ有効。
select setval('sample_seq', 1);値設定。

psqlが内部で発行しているsqlを表示する

psqlに「-E」オプションを付けて起動する。

psql -E
testdb=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
 c.relname as "Name",
 CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
 u.usename as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
     AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

bytea型を\copyで出力した形式と同等な文字列を生成する

bytea型を\copyで出力すると「\\000\\001...」とエスケープされるが、 同等の処理をする関数が存在しない。
例えば、PHPのpg_escape_bytea()は、いくつかの制御文字をエスケープしないので、 TSV形式に出力できない。

そこで、以下の関数を利用すれば、\copy句の高速インポートができるようになる。

  • PHPでの例
    	/**
    	* バイナリデータをPostgreSQLの\copy形式でエスケープする
    	*
    	* @param mixed &$data	: バイナリデータ
    	* @return mixed $result	: エスケープされた文字列
    	*
    	* @note
    	* 以下のようなpsqlの\copyコマンドで読み込める形式にバイナリを変換する事ができる。\n
    	* @code
    	* \copy テーブル名 from 'tsvファイル名' with delimiter as '\t' null as ''
    	* @endcode
    	*
    	* @notice
    	* \copyコマンドで使うことを目的。\n
    	* 標準のpg_escape_bytea()はいくつかの制御文字列(\t,\r,\n等)をエスケープしてくれないため、
    	* 改行を1行の区切りとした、tsv形式等に利用できない。\n
    	* ただし、通常のsql文にはpg_escape_bytea()を使う事。\n
    	*/
    	function pg_escape_bytea_copy(&$data)
    	{
    		$result = null;
    		foreach(unpack("C*", $data) as $key => $val){
    			if( $val == 0134 ){
    				$val = '\\\\\\\\';
    			}else if( (000 <= $val && $val <= 037) || (0177 <= $val && $val <= 0377) ){
    				$val = sprintf("\\\\%03o", $val);
    			}else{
    				$val = pack("C", $val);
    			}
    			$result .= $val;
    		}
    		return $result;
    	}

標準的な設定

$ su - postgres
$ cd data
$ vi postgresql.conf

tcpip_socket = true

$ vi pg_hba.conf

# Unix ドメインソケットを使用する接続
local   all         all                                             trust

# TCP/IP ネットワークを使用する接続
host    all         all         127.0.0.1         255.255.255.255   trust 
$ pg_ctl restart

PostgreSQL 7.4 -> 7.3へリストアする

7.3でバックアップしたデータを、7.3へリストアするとエラーが頻発します。
どうやら、シーケンスの構文が違うようです。
そこで、PLAINテキストで出力し、

pg_dump データベース名 > データベース名.backup

以下の文字を削除します。

   INCREMENT BY 1
   NO MAXVALUE
   NO MINVALUE

PostgreSQL 7.3で文字化けする場合

データベースがUNICODE(UTF-8)、PHPがEUC-JPの場合、PostgreSQL7.3と7.4では挙動が違います。

PostgreSQL文字化けの有無
7.3有り
7.4無し

どうも7.4ではPHP内部文字コードに合わせて自動で変換してくれるようです。
7.3では「pg_set_client_encoding("EUC_JP");」等明示的に指定してあげないと、文字化けします。
 内部文字コードを取得し、PostgreSQLに渡す例

pg_set_client_encoding(mb_internal_encoding());

Oracleから移植する時のTIPS

Oracleから移植する際に同等の事をする関数は、

OraclePostgreSQL
SYSDATECURRENT_TIMESTAMP
NVLCOALESCE
DECODECASE 〜 WHEN 〜 THEN

to_date()とto_timestamp()の違い

Oracleと違い、日付関数により結果が違うので注意。
よって、timestamp型のフィールドと比較する際はto_timestamp()を用いる事。

関数結果
to_date('2005/01/27 20:27:01', 'YYYY/MM/DD HH24:MI:SS')2005/01/27
to_timestamp('2005/01/27 20:27:01', 'YYYY/MM/DD HH24:MI:SS')2005/01/27 20:27:01

クライアントの文字コードを指定する

DBがUnicodeでターミナルがEUCだとselect時に文字化けします。
これを指定するには「PGCLIENTENCODING」を設定します。

  • bashの場合
    .bash_profile に以下の行を追加します。
    export PGCLIENTENCODING=EUC-JP
  • windowsの場合
    batファイル等の中で、
    set PGCLIENTENCODING=EUC-JP

phpPgAdmin?

文字化けする場合は

/etc/php.iniのmbstring.internal_encodingやmbstring.http_outputを確認する。
mbstring.http_outputがEUC-JPでも、mbstring.internal_encoding=UTF-8にする事で文字化けを解消できた。

/etc/httpd/conf/httpd.conf

<Directory "/var/www/html/phpPgAdmin">
       php_value mbstring.internal_encoding UTF-8
</Directory>

httpdの再起動。

service httpd restart

設定ファイル

phpPgAdmin/conf/config.inc.php
  • パスワード無しでもログイン可能に
    $conf['extra_login_security'] = false;
  • デフォルト言語を日本語に
    $conf['default_lang'] = 'japanese';
  • 接続用設定

設定を追加するには、$conf['servers'][1]['desc']などと添え字を増やす。

変数名意味
$conf['servers'][0]['desc']表示上の名前
$conf['servers'][0]['host']接続先名 or ip。空白でローカルホストに接続
$conf['servers'][0]['port']ポート。デフォルト5432
$conf['servers'][0]['defaultdb']デフォルトで接続するDB
$conf['servers'][0]['pg_dump_path']pg_dumpのパス
$conf['servers'][0]['pg_dumpall_path']pg_dumpallのパス

PostgreSQL操作ツール

pgAdminIIIhttp://www.pgadmin.org/download/windows.phpWindows用クライアント。8.0対応、日本語可。一応7.4も操作できた。
phpPgAdmin?http://phppgadmin.sourceforge.net/?page=downloadphp上で操作するツール。7.4、8.0対応。日本語可。
csehttp://www.hi-ho.ne.jp/tsumiki/PostgreSQL、Oracle、MySQL、ODBC等扱えるツール。日本語。

TSV(タブ区切りテキスト)ファイルへのインポート/エクスポート

  • インポート
    \copy テーブル名 from 'tsvファイル名' with delimiter as '\t' null as ''
  • エクスポート
    \copy テーブル名 to 'tsvファイル名' with delimiter as '\t' null as ''

CSVファイルへエクスポート

psqlの\copyコマンドを使う。

\copy テーブル名 to 'csvファイル名' with delimiter as ',' null as ''

CSVファイルのインポート

psqlの\copyコマンドを使う。
カンマの数とテーブルのカラム数は一致させる必要がある。
漢字が含まれているファイルはEUC-JP/LFにしておく必要がある。
「null as ''」句でNULLを挿入できる。

\copy テーブル名 from 'csvファイル名' with delimiter as ',' null as ''

iptablesの設定

psqlでTCP/IP接続でエラーが出る場合は、iptablesを確認する。

  • iptables
    # vi /etc/sysconfig/iptables
    -A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
    
    # service iptables restart

psql -U "ユーザー名" -d "db名"で接続できないとき

以下のようなエラーが出て、接続できない場合は、pg_hba.confを編集し、PostgreSQLを再起動させます。
標準の認証はIDENT認証なので、これをpasswordやmd5にします。

エラー:

-bash-2.05b$ psql -U ユーザー名 -d データベース名
psql: FATAL:  IDENT authentication failed for user "ユーザー名"

修正:

$ vi /var/lib/pgsql/data/pg_hba.conf

# ident認証からパスワード認証へ変更
#local  all    all             ident   sameuser
local  all    all             password   sameuser

$ pg_ctl restart

OracleのスキーマをPostgreSQLで使えるSQLに変換する

PostgreSQLのソース中にPerlで書かれたツールがあります。

postgresql-7.4.6/contrib/oracle/ora2pg.pl

既存のOracleDBに接続して、PostgreSQLのSQLに変換してくれます。
ora2pg.pl を開いて、下記を修正します。

BEGIN {
    $ENV{ORACLE_HOME} = 'オラクルホームのパス';
}
# Initialyze the database connection
my $dbsrc = 'dbi:Oracle:host=localhost;sid=OracleのSID';
my $dbuser = 'ユーザー名';
my $dbpwd = 'パスワード';
my $schema = new Ora2Pg (
debug => 0,
...
);

実行は以下の通りです。成功すると、output.sqlが生成されます。

perl ora2pg.pl

PostgreSQL7.3からdatetime型が使えなくなった

PostgreSQL7.3からdatetime型が使えなくなり、timestamp型に変わった。
ただ、それでは困る場合、ユーザー定義型で強引にdatetime型を作ってしまう方法がある。

INSERT INTO pg_catalog.pg_type SELECT 'datetime', typnamespace, typowner,
typlen, typbyval, typtype, typisdefined, typdelim, typrelid, typelem,
typinput, typoutput, typreceive, typsend, typalign, typstorage,
typnotnull, typbasetype, typtypmod, typndims, typdefaultbin, typdefault
from pg_catalog.pg_type where typname = 'timestamp';

INSERT INTO pg_catalog.pg_type SELECT '_datetime', typnamespace,
typowner, typlen, typbyval, typtype, typisdefined, typdelim, typrelid,
typelem, typinput, typoutput, typreceive, typsend, typalign, typstorage,
typnotnull, typbasetype, typtypmod, typndims, typdefaultbin, typdefault
from pg_catalog.pg_type where typname = '_timestamp';

Windows用コマンドラインツール

・Linuxと同じようなpsqlコマンドがwindowsで使えるようになる。
http://cre-ent.skcapi.co.jp/~saito/developer/PostgreSQL-7.4.3/psql-windows/

ODBCドライバについて

開発元が異なるバージョンが複数存在する


添付ファイル: fileget_nocase_text.php 2958件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-04-10 (火) 22:07:31 (75d)