yoo-s.com

Mac に PostgreSQL をインストール

PostgreSQL 2020/08/09

Docker や VirtualBox ではなく Mac に PostgreSQL をインストールしてみます。

Homebrew から PostgreSQL 最新版をインストール

Homebrew を更新してから PostgreSQL 最新版をインストールします。


$ brew update
$ brew install postgresql

パスを設定します。


$ brew link postgresql

postinstall をします。


$ brew postinstall postgresql

インストールが完了したら、バージョンを確認します。


$ postgres --version
psql (PostgreSQL) 12.3

データベースを初期化（UTF-8）します。


$ initdb /usr/local/var/postgres -E utf8

PostgreSQL を起動します。


$ brew services start postgresql

PostgreSQL の起動を確認します。


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

DB データのパスを設定

DB データのパスを環境変数に設定します。


$ echo export PGDATA=/usr/local/var/postgres >> ~/.bash_profile
$ source ~/.bash_profile

~/.bash_profile に書き込まれます。

DB を作成する

createdb でデータベースを作成します。


$ createdb sample

DB が作成されているのを確認します。


$ psql -l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 postgres  | yoo   | UTF8     | C       | C     | 
 sample    | yoo   | UTF8     | C       | C     | 
...

MacからLaradock PostgreSQLで接続エラー

PostgreSQL 2020/01/21

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'

pgAdimn4 でブラウザで開けなくなる

PostgreSQL 2019/09/28

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

pgAdmin4 メニューのURLコピー

pgAdmin4 メニューコピー後にブラウザでアクセス

pgAdmin4 シャットダウン

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

ロケールの再構築

PostgreSQL 2019/08/27

文字コードを 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

各時間毎の累積データ

PostgreSQL 2018/07/19

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

INSERT成功時にカラムデータを取得

PostgreSQL 2018/07/02

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

pg_constraint で外部キー情報取得

PostgreSQL 2017/08/30

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]

テーブル一覧を取得

PostgreSQL 2017/07/12

テーブル一覧は「pg_tables」で取得できるが、relfilenode などの細かい情報まではわからない。

「pg_class」「pg_tables」「pg_namespace」を利用するとある程度細かい情報まで取得できる。

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

SELECT * FROM pg_class LEFT JOIN pg_tables ON pg_tables.tablename = pg_class.relname LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = 'r' AND relfilenode > 0 AND nspname = 'public';

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