PostgreSQL TEXT型からINTEGER型に変換する方法

そのままだとエラーだったけどキャストしたらいけた。

ALTER TABLE [table] ALTER COLUMN [column] TYPE INTEGER USING ( [column]::INTEGER );

PotgreSQL NULLで文字列連結すると結果NULLだけになる

psql> SELECT 'hogehoge' || '-' || null_col FROM hoge;

これだと結果は hogehoge ではなく null になってしまう。

これは　COALESCE　という関数で対処する。

psql> SELECT 'hogehoge' || '-' || COALESCE(null_col, '') FROM hoge;

これで結果は hogehoge となるはず。

PostgreSQL テーブルのカラムに付けたコメントをpsqlから確認する方法

せっかくカラムにコメント付けたのに\dで確認できないよ。。。
って時はこれ！！！

\d+ table_name

これでコメントも一緒に表示されます♪

もちろテーブル名を指定しなければテーブルに付けたコメントが表示されます。

PostgreSQL クエリに対するコストの確認方法

Explainを利用して確認する方法もあるがこんな方法もある。

\timing

と入力してタイミングモードをonにしてから通常通りSELECT文などを実行すると、SQLの実測値がわかる。

ちなみに

\! pwd

など、「\!」の後にコマンドを入力するとわざわざpsqlを終了しなくても大丈夫みたい。
知らなかった。。。

PostgreSQL VS MySQL

毎回気になるこの比較
バージョンアップするにつれてその差は変化するため今回のメモをここに。

・PostgreSQLの方がカラム数が少ない場合INSERTが早いが、多い場合は逆にMｙSQLの方が早い。
※PostgreSQL8.2でこの現象は修正されているようだが、修正されたかによってカラム数が少ない時に早くならない可能性も。。。

・SELECTはPostgreSQLの方が早い。

・UPDATEはMySQLの方が早い。

・DELETEはPostgreSQLの方が早い。
※論理削除だから。 そのせいでバキュームが必要になってくる。。。

・サーバーとクライアントが別PCの場合MySQLは少し遅い。

・１サーバー　多クライアントの場合PostgreSQLは性能が劣化しにくいがMySQLはInnoDBを利用しないと非常に遅いらしい。

CentOS5.4 Postgresql8.4系を入れた

/etc/yum.repos.d/CentOS-Base.repo　を編集。

[base]と[update]に　exclude=postgresql*　を追記

そして下記を実行

cd /usr/local/src/yumrpm

wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm
rpm -ivh pgdg-centos-8.4-1.noarch.rpm

yum install postgresql postgresql-server postgresql-devel compat-postgresql-libs postgresql-libs postgresql-contrib

yum -y --enablerepo=remi,epel,rpmforge install php-pgsql

service postgresql initdb

Data directory is not empty postgresql!　と出てしまった。。。

cd /var/lib/pgsql
mv data data_bak

リトライ！！

service postgresql initdb

よし♪

service postgresql start

自動起動の設定

chkconfig postgresql

PostgreSQL UNIQUE制約の削除

これらは全部ダメ

×　ALTER TABLE [table_name] ALTER COLUMN [column_name] DROP INDEX [index_name];
×　ALTER TABLE [table_name] ALTER COLUMN [column_name] DROP UNIQUE;
×　ALTER TABLE [table_name] DROP INDEX [index_name];
×　DROP INDEX [index_name];

これが正解

○　ALTER TABLE [table_name] DROP CONSTRAINT [column_name];

Windows CSEを利用してPostgreSQLに接続する

まずPostgreSQLのドライバをインストールする。　

検索　＞　ダウンロード


pgAdminⅢを起動してパスワードを入力してログインする。

データベースを選択して接続する。

 接続するとツールサーバー構成が選べるようになるので、そこにある　pg_hba.conf　を選択する。

127.0.0.1　の設定されているレコードをダブルクリックする。

方法という項目がmd5になっているので、password に変更する。

そして上書き保存してCSEで接続可能かを確認する。
※CSEでユーザー名、パスワードを入力するようになっているがODBCの設定で入力していれば必要なし。

今回参考にしたサイトはこちら

UPDATE でJOINする方法(PostgreSQL)

2つのテーブルにまたがってユニークとなるキーが存在した場合に必要となった。
PostgreSQLでは対応可能であったのでメモ。

UPDATE
    hoge1
SET
    flag = 1
FROM
    hoge2
WHERE
    hoge2.code = hoge1.code
AND
    TRANSLATE(hoge2.col1, '-', '') || hoge1.col2 = 'testtest'

★ポイントはココ

FROM
    hoge2
WHERE
    hoge2.code = hoge1.code