カテゴリー : PostgreSQL

[Heroku] heroku-postgresql:dev の設定方法

Heroku にて、従来のshared-database から新しくなった heroku-postgresql の dev をインストールしました。

$ heroku addons:add heroku-postgresql:dev
----> Adding heroku-postgresql:dev to myapp... done, v5 (free)
      Attached as HEROKU_POSTGRESQL_SILVER
      Database has been created and is available
        ! WARNING: dev is in beta
        !          increased risk of data loss and downtime
        !          send feedback to dod-feedback@heroku.com
$ heroku pg:info HEROKU_POSTGRESQL_SILVER
=== HEROKU_POSTGRESQL_SILVER
Plan         Dev
Status       available
Connections  1
PG Version   9.1.3
Created      2012-06-01 14:23 UTC
Data Size    0
Tables       0
Rows         0
Conn Info    "host=ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com
             port=5432 dbname=xxxxxxxxxxx
             user=xxxxxxxxxx sslmode=require
             password=xxxxxxxxxxxxxxxxx"
$ heroku pg:promote HEROKU_POSTGRESQL_SILVER
-----> Promoting HEROKU_POSTGRESQL_SILVER to DATABASE_URL... done

以上です。

【参考】

Heroku’s new, free PostgreSQL 9.1 development database はてなブックマーク - Heroku's new, free PostgreSQL 9.1 development database

Heroku Postgres Dev Plan | Heroku Dev Center はてなブックマーク - Heroku Postgres Dev Plan | Heroku Dev Center

Postgres | Heroku Dev Center はてなブックマーク - Postgres | Heroku Dev Center

heroku-postgresql:dev に移行した – HsbtDiary(2012-05-16) はてなブックマーク - heroku-postgresql:dev に移行した - HsbtDiary(2012-05-16)

ヘロク、SaaS型DB「Heroku Postgres」の安価なプランをベータ公開 (Computerworld) – Yahoo!ニュース はてなブックマーク - ヘロク、SaaS型DB「Heroku Postgres」の安価なプランをベータ公開 (Computerworld) - Yahoo!ニュース

[PostgreSQL] ERROR: Cannot insert a duplicate key into unique index

PostgreSQLにて、新規レコードのINSERT時に下記のようなエラーが発生しました。

ERROR: Cannot insert a duplicate key into unique index tbl_xxxx_pkey

原因は、既にDBに存在しているレコードのカラム xxxx_pkey がUNIQUE制約に引っ掛かっていたためでした。

UNIQUE 制約

[ CONSTRAINT name ] UNIQUE

入力

CONSTRAINT name
制約に付与する任意の名前。

出力

status
ERROR: Cannot insert a duplicate key into a unique index.
このエラーは、列に重複する値を挿入しようとした、 その実行時に起こります。

・引用元:CREATE TABLE はてなブックマーク - CREATE TABLE

以上です。

【参考】

ポストグレスSQLメモ(Hishidama’s PostgreSQL SQL Memo) はてなブックマーク - ポストグレスSQLメモ(Hishidama's PostgreSQL SQL Memo)

[PostgreSQL] 他のPCから接続するための設定

PostgreSQL にて、他ホストから接続するための設定を調べたのでメモ。

postgresq.confファイルの修正

■ /usr/local/pgsql/data/postgresql.conf

#listen_addresses = 'localhost' # what IP interface(s) to listen on; 
                                # defaults to localhost, '*' = any

↓(変更)

listen_addresses = '*'

設定を変更後、PostgreSQLを再起動する。

/etc/rc.d/init.d/postgresql restart

接続できるクライアントを設定する

PostgreSQLの設定ファイルである pg_hba.conf にて、接続できるホストを制限しているので、これを変更します。

■ /usr/local/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

■(例)全てのホストからの接続を許可する

全てのホストから、全てのデータベースへの接続を許可する場合、以下の記述を追加する。

host all all 0.0.0.0/0 trust

接続を制限する

pg_hba.confは1行で1レコードを構成しており、1行のフォーマットは以下のとおりである。

KIND DATABASE USER CIDR-ADDRESS METHOD

● KIND

「local」「host」「hostssl」「hostnossl」のいずれか。localはUNIXドメインソケット経由の接続、hostはTCP/IP経由の接続、hostsslはSSL経由の接続、hostnosslはSSLを使用しない接続を意味する。

● DATABASE
接続を許可したいデータベース名を指定。「all」とすると全てのデータベースを意味する。複数のデータベース名を指定したい場合は、カンマで区切る。

● USER
接続を許可するユーザ名を指定。「all」とすると全てのユーザを意味する。ユーザ名の前に「+」を付けるとグループ名を指定したことになる。複数のユーザ名を指定したい場合は、カンマで区切る。

● CIDR-ADDRESS
KINDがlocal以外のときに指定する。接続を許可するクライアントのIPアドレスやネットワークアドレスを指定する。

例1)192.168.0.11/32・・・192.168.0.11からの接続のみを許可する
例2)192.168.0.0/24・・・IPアドレスが192.168.0.xであるクライアントのみを許可する
例3)0.0.0.0/0・・・任意のIPアドレスのクライアントを許可する

● METHOD
ユーザの認証方式を指定する。代表的なものは以下のとおり。
trust・・・認証なし。無条件に接続を許可する。
reject・・・無条件に接続を拒否する。特定のホストやネットワークからの接続を拒否する際に使用。
md5・・・md5を利用したパスワード認証。パスワードを指定していないユーザは接続できない。
password・・・パスワード認証を行うが、BASIC認証のため、パスワードがそのままネットワークを流れてしまう。

・引用元:他ホストから接続するための設定 はてなブックマーク - 他ホストから接続するための設定

以上です。

【参考】

PostgreSQL8.3 クライアント認証 – 読書と技術となんか色々のログ – 楽天ブログ(Blog) はてなブックマーク - PostgreSQL8.3 クライアント認証 - 読書と技術となんか色々のログ - 楽天ブログ(Blog)

[PostgreSQL] NULL でない自身の最初の引数を返す COALESCE 関数が便利

PostgreSQL にて、値がNULLの場合は、代わりの値を返す COALESCE 関数という便利なものを教えてもらったのでメモ。

SUM 関数は、指定したカラム(ここでは money)の値が全て NULL の場合、NULLを返します。

返り値が NULL ではなく、0 にしたいので、最初は下記のように書いていました。

SELECT
	CASE WHEN SUM(money) IS NOT NULL THEN SUM(money) ELSE 0 END AS total_money
	FROM USER;

このような、SUM関数の結果がNULLの場合は、0 を返して欲しいという書き方は、CASE 文を使わなくても、COALESCE 関数を使えば実現できるみたいです。

6.12.2. COALESCE
COALESCE(value [, …])
COALESCE 関数は、NULL でない自身の最初の引数を返します。 データを表示の目的で取り出す際、NULL 値の代わりにデフォルト値を使う場合、時として便利なことがあります。 その例です。

SELECT COALESCE(description, short_description, ‘(none)’) …

・引用元:条件式 はてなブックマーク - 条件式

なので、COALESCE 関数を使って、下記のように書くことができます。

SELECT
	COALESCE(SUM(money), 0) AS total_money
	FROM USER;

以上です。

【参考】

PostgreSQL COALESCEでNULLを空白に変換 – 130単位 はてなブックマーク - PostgreSQL COALESCEでNULLを空白に変換 - 130単位

SQL の COALESCE はてなブックマーク - SQL の COALESCE

[PostgreSQL] ERROR: permission denied for ~ というエラーが出たらテーブル(table)の権限を確認する

PostgreSQL にて “ERROR: permission denied for ~” というエラーが出たら、テーブル(table)の権限がないということなのでこれを確認します。

■ エラー内容

ERROR:  permission denied for table table_name

■ 権限の確認コマンド

\z

■ テーブルへの権限付与(例)

GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;

以上です。

【参考】

ふかふかの部屋 – TSR – PostgreSQL 関連 はてなブックマーク - ふかふかの部屋 - TSR - PostgreSQL 関連

権限の追加 – ロール(ユーザー)の作成 – PostgreSQLの使い方 はてなブックマーク - 権限の追加 - ロール(ユーザー)の作成 - PostgreSQLの使い方