カテゴリー : PostgreSQL

psql したら Library not loaded: libreadline エラーが出たので brew upgrade postgresql した

ひさしぶりに PostgreSQL を使おうと思い psql コマンドを実行したら Library not loaded: libreadline エラーが出たので brew upgrade postgresql して解決したというお話です。

psql コマンド実行で Library not loaded: libreadline エラー

$ psql
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
  Referenced from: /usr/local/bin/psql
  Reason: image not found
[1]    61053 trace trap  psql

brew upgrade postgresql で PostgreSQL のバージョンアップ

brew update
brew upgrade postgresql
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
mv /usr/local/var/postgres /usr/local/var/postgres9.3.1
initdb /usr/local/var/postgres
pg_upgrade \
  -b /usr/local/Cellar/postgresql/9.3.1/bin \
  -B /usr/local/Cellar/postgresql/9.6.1/bin \
  -d /usr/local/var/postgres9.3.1 \
  -D /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/9.6.1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

たまに PostgreSQL を使うぞってときに version アップデートからハマりたくはないですよね。

参考情報

[PostgreSQL] postmaster.opts は自動生成されるファイルなので編集しても無駄

PostgreSQL 7.0 で postmaster の起動時に -i オプションを追加する変更をするために postmaster.opts を修正していたのですが、postgres を再起動すると編集前の状態に戻っていました。

PostgreSQL

どうやら、自動生成されるファイルなので編集しても無駄みたいでした。

続きを読む

[PostgreSQL] ERROR: language “plpgsql” does not exist

PostgreSQL で CREATE FUNCTION したら下記のようなエラーが発生しました。

ERROR:  language "plpgsql" does not exist
HINT:  You need to use "createlang" to load the language into the database.

plpgsql を後から追加するときは、コマンドラインから createlang コマンドでDB名を指定して実行すれば良いみたいです。

$ createlang -d database_name plpgsql

以上です。

【参考】

PostgreSQL で plpgsql がないときは | dTblog | デザインとプログラムの境界をさまようブログ はてなブックマーク - PostgreSQL で plpgsql がないときは | dTblog | デザインとプログラムの境界をさまようブログ

[PostgreSQL] ERROR: must be owner of extension plpgsql

PostgreSQL 8.0.4 から 9.1.5 にDBの移行作業で、pg_dump したデータをインポートしたら下記のようなエラーが発生しました。

CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
 
WARNING:  no privileges could be revoked for "public"
WARNING:  no privileges were granted for "public"

DB を操作しているユーザに「スーパーユーザ」の権限を追加することで解決しました。

■ スーパーユーザ権限を追加する

postgres=# \du
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー 
----------+----------------------------------------------------------------------+----------
 newuser   | DBを作成できる                                                       | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}
 
postgres=# ALTER USER newuser CREATEUSER ;
ALTER ROLE
 
postgres=# \du
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー 
----------+----------------------------------------------------------------------+----------
 newuser   | スーパーユーザ, DBを作成できる                                       | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}

以上です。

【参考】

トリガーやファンクションのリストアが失敗していた。 – マンドクセェメモ はてなブックマーク - トリガーやファンクションのリストアが失敗していた。 - マンドクセェメモ

CREATE EXTENSION はてなブックマーク - CREATE EXTENSION

[PostgreSQL] ERROR: missing FROM-clause entry for table

PostgreSQL にて ERROR: missing FROM-clause entry for table というエラーが発生した場合の対応方法をご紹介します。

PostgreSQL

続きを読む

[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;

以上、PostgreSQL の ERROR: permission denied for に負けいない、現場からお送りしました。

[PostgreSQL][8.0.4] シーケンス関数の引数は regclass型ではなく text型 (ERROR: cannot cast type text to regclass)

PostgreSQL 8.0.4 にて、nextval((‘user_id_seq’::text)::regclass) というように、シーケンス関数の引数を text型ではなく、regclass型を渡してしまったため、エラーが発生しました。

■ エラー内容

ERROR:  cannot cast type text to regclass

■ 参考情報

注意: PostgreSQL 8.1以前においては、シーケンス関数の引数はregclass型ではなく、text型で、そして上記のテキスト文字列からOID値への変換はそれぞれの呼び出し実行時に起こりました。後方互換性のため、この仕組みはまだ存在しますが、内部的には関数が実行される前にtextからregclassへの暗黙的強制型変換として現在処理されています。

・引用元:シーケンス操作関数 はてなブックマーク - シーケンス操作関数

■ SQL文(修正前)

CREATE SEQUENCE user_id_seq INCREMENT 1 START 1000;
 
CREATE TABLE user ( 
	id integer DEFAULT nextval(('user_id_seq'::text)::regclass) NOT NULL,
	name varchar(50) NOT NULL,
	age integer NOT NULL
);

↓(修正)

■ SQL文(修正後)

CREATE SEQUENCE user_id_seq INCREMENT 1 START 1000;
 
CREATE TABLE user ( 
	id integer DEFAULT nextval('user_id_seq') NOT NULL,
	name varchar(50) NOT NULL,
	age integer NOT NULL
);

以上です。