Laradock の接続は psql のバージョンが 12 だとプロトコルエラーとなった。

$ psql -h localhost -U default -l
psql: error: could not connect to server: FATAL:  unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0

Macにインストールした psql のバージョンを変更する。

psql のパスを確認

$ which psql
/usr/local/opt/libpq/bin/psql

Homebrew で任意のPostgreSQLをインストール

$brew search postgresql
...
$ brew install postgresql@11

エイリアスを追加

$ alias psql='/usr/local/opt/postgresql@11/bin/psql'

pgAdmin4 からブラウザで起動することになった。
ただポートを自動生成してブラウザ起動するので、ブラウザを閉じてURL（ポート）がわからないと開けないという仕様 pgAdmin4アプリ自体をクリックしても開かないので以下で対応 - pgAdmin4 メニューのURLコピー - pgAdmin4 シャットダウン

pgAdmin4 メニューのURLコピー

コピー後にブラウザでアクセス

pgAdmin4 シャットダウン

それでもひらけない場合は、シャットダウンして再起動

文字コードを en_US.UTF-8 から ja_JP.UTF-8 に変更する方法
※この処理は DBが全てリセットされてしまうので注意！

# localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8 # vi /etc/locale.gen ja_JP.UTF-8 UTF-8 # update-locale LANG=ja_JP.UTF-8

再ログイン

# locale LANG=ja_JP.UTF-8 LANGUAGE= LC_CTYPE="ja_JP.UTF-8" LC_NUMERIC="ja_JP.UTF-8" LC_TIME="ja_JP.UTF-8" LC_COLLATE="ja_JP.UTF-8" LC_MONETARY="ja_JP.UTF-8" LC_MESSAGES="ja_JP.UTF-8" LC_PAPER="ja_JP.UTF-8" LC_NAME="ja_JP.UTF-8" LC_ADDRESS="ja_JP.UTF-8" LC_TELEPHONE="ja_JP.UTF-8" LC_MEASUREMENT="ja_JP.UTF-8" LC_IDENTIFICATION="ja_JP.UTF-8" LC_ALL=

PostgreSLQ 停止 & クラスター削除

# /etc/init.d/postgresql stop # pg_dropcluster --stop 11 main

PostgreSLQ クラスター作成 & 起動

# pg_createcluster 11 -e UTF-8 --locale ja_JP.UTF-8 --start main # /etc/init.d/postgresql start

DB確認

$ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres

SQLで累積を計算する場合、OVER句を利用すると便利

1ヶ月の降水量を各時間ごとに累積

データ

1時間毎の雨量を INSERT datetime, precipitation 2018-06-01 00:00, 0.0 2018-06-01 01:00, 0.1 2018-06-01 02:00, 0.3 ....... 2018-06-30 22:00 1.2 2018-06-30 23:00 1.3

SQL

datetime を昇順でソートし、降水量累計 [sql] SELECT datetime, precipitation, SUM(precipitation) OVER(ORDER BY datetime ASC) AS sum_precipitation FROM rainfalls WHERE datetime >= '2018-06-01 00:00' AND datetime < '2018-07-01 00:00' ORDER BY datetime [/sql]

計算後

datetime, precipitation, sum_precipitation 2018-06-01 00:00, 0.0, 0.0 2018-06-01 01:00, 0.1, 0.1 2018-06-01 02:00, 0.3, 0.4 ....... 2018-06-30 22:00 1.2, 11.1 2018-06-30 23:00 1.3, 12.4

SQL INSERT した後にプライマリIDを取得したい場合、 RETURNING を利用すると便利 [sql] INSERT INTO users (name, age) VALUES ('Yoo', '26') RETURNING id; [/sql] RETURNING の後は「*」なども指定できる。

pg_constraint の内部キー、外部キーは、pg_class、pg_attribute にアクセスしないと取得できない。 ・contype = 'f' ・conrelid（内部テーブル）：smallint ・confrelid（外部テーブル）：smallint ・conkey（内部キー）： smallint[] ・confkey（外部キー）： smallint[] まず、内部キーテーブルを作成して、副問い合わせで外部キーも連結させる。 内部キー、内部キーは数値配列なので抽出に「ANY」を利用する。 [sql] SELECT origin.*, pg_class.oid as foreign_class_id, pg_class.relname as foreign_relname, pg_attribute.attnum as foreign_attnum, pg_attribute.attname as foreign_attname FROM ( SELECT pg_constraint.conrelid , pg_class.oid as pg_class_id , pg_class.relname , pg_attribute.attnum , pg_attribute.attname , pg_constraint.confrelid , pg_constraint.confkey , pg_constraint.contype FROM pg_constraint LEFT JOIN pg_attribute ON pg_constraint.conrelid = pg_attribute.attrelid AND pg_attribute.attnum = ANY(pg_constraint.conkey) LEFT JOIN pg_class ON pg_constraint.conrelid = pg_class.oid WHERE pg_constraint.contype = 'f' AND pg_constraint.conrelid = 'pg_classのID' ) AS origin LEFT JOIN pg_attribute ON origin.confrelid = pg_attribute.attrelid AND pg_attribute.attnum = ANY(origin.confkey) LEFT JOIN pg_class ON origin.confrelid = pg_class.oid ; [/sql]

テーブル一覧は「pg_tables」で 取得できるが、relfilenode などの細かい情報まではわからない。 「pg_class」「pg_tables」「pg_namespace」を利用するとある程度細かい情報まで取得できる。

publicのテーブル一覧情報取得

*「relkind = 'r'」テーブル指定

PostgreSQL9.1でDBに接続できるが、pg_connect()のlocalhost経由で拒否された。 どうやら、pg_hba.conf にIPv6のlocalhost の設定を記述しないといけないらしい。

host all all ::1/128 trust

何だか納得いかないが、IPv6で接続しているんだろうか？

全てのテーブルを削除する場合、カスケードを設定しているとテーブル削除が面倒 以下のSQLでDBを作り直さずに、テーブルを全て削除する事ができる。

DROP SCHEMA public DASCADE; CREATE SCHEMA public;

参考

Drop all tables in postgresql?

bz2で圧縮されたSQLファイルをリストアするには、bzcatコマンド（インストール必要）とpsqlコマンドをパイプすることで可能

bzcat データベース.bz2 | psql データベース名