備忘録的プログラミングリファレンス

PostgreSQL リファレンス

 PostgreSQL は、リレーショナル(関係)データベース管理システム (RDBMS:relational database management system) です。
 SQL 言語を使用してデータベースおよびテーブルなどを作成しデータを記録することができます。
 デーモン機能を使ってデータベースサーバーとすることも、単独でデータベースとしても利用できます。

 PostgreSQL のユーザー管理は、ユーザーではなくロールで行います。PostgreSQL には、データベースごとにロールを分けることができないという特徴があります。
 PostgreSQL ではロールはシステム内で統一されて管理されます。そのため、異なる会社やサイトといった複数の異なるデータベースの管理ユーザーを混在させるような使用方法には向いていません。
 例えば、複数の会社(管理ユーザーが異なる場合)を1つのサーバーで管理するのではなく、会社(管理ユーザー)ごとにサーバーを構築する必要があります。

PostgreSQL のインストール

 ここでは Ubuntu における PostgreSQL のインストールからユーザーの設定、PostgreSQL へのログインについて簡単に解説します。

 インストールには Ubuntu や CentOS などのディストリビューションからインストールする方法と、PostgreSQL のサイトからソースをダウンロードしインストールする方法があります。
 開発環境をディストリビューションを利用して構築するのなら、PostgreSQL もディストリビューションを利用してインストールすることをお勧めします。

PostgreSQLのインストール

 管理者権限で、aptによってインストールします。

$ sudo apt install postgresql

 インストールしただけの状態で公のサーバーとして利用するなら問題があります。スーパーユーザーの設定、アクセスログの記録などのセキュリティー設定や記録ディレクトリの設定などが必要になります。

 初期のスーパーユーザーはpostgresになっています。さらにパスワードを設定しなければなりません。
 詳しくは初期状態でのpostgresユーザーによるログインを参照して下さい。

PostgreSQLの開発環境のインストール

 もし開発環境を構築する必要がある場合には、上記の方法では開発用パッケージは含まれていません。PostgreSQLを使って開発を行うにはpostgresql-server-dev-all, libpq-devパッケージもインストールする必要があります。

$ sudo apt install postgresql-10 postgresql-server-dev-10 libpq-dev

詳しくはPostgreSQLのインストールについてを参照してください。

 パッケージインストールによる MySQL はインストールするだけでサーバーとして自動で起動するようになっています。

 起動しているかを確認するには systemctl コマンドのオプション status を使用します。

$ systemctl status postgresql
 postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Wed 2023-09-13 07:44:13 JST; 13min ago
    Process: 1413 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1413 (code=exited, status=0/SUCCESS)
        CPU: 1ms

 9月 13 07:44:13 user-PC systemd[1]: Starting PostgreSQL RDBMS...
 9月 13 07:44:13 user-PC systemd[1]: Finished PostgreSQL RDBMS.

 上記のように、PostgreSQL サーバーがactiveになっていればログインが可能になります。

 PostgreSQL を起動するためには systemctl コマンドのオプション start を使用します。

$ sudo systemctl start postgresql

 サーバーの起動、再起動に関しては起動設定を参照してください。

PostgreSQLの操作( psql )

 psql は端末から利用できる PostgreSQL 用のインターフェイスです。

 OS の端末から psql コマンドでPostgreSQLのコマンドラインインターフェイスにログインします。

psqlでログイン
$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
...

 psql コマンドでデータベース名を指定しない場合はユーザー名と同じデータベース名に接続されます。

 詳しくはpsqlを参照して下さい。

 初期状態ではpostgresで PostgreSQL のコマンドラインインターフェイスにログインしてみると思います。 しかし、初期状態のpostgresユーザーは Ubuntu においてはパスワードが設定されていません。
 詳しくは初期状態でのpostgresユーザーによるログインを参照して下さい。

 PostgreSQL からログアウトする場合は以下のコマンドです。

psqlからのログアウト
=# \q

ユーザー(ロール)の管理

 PostgreSQLでは、ユーザーはロールとして管理します。ver 8.1 より後では、ユーザーによる PostgreSQL データベースへの認証はできません。 データベースへのログインや操作権限はロールで管理します。

 PostgreSQLへのログインや操作権限はユーザーごとに管理するのではなく、ロールごとに管理します。ロールにロールを登録することでグループとして管理することもできます。
 ロールは PostgreSQL サーバーで一括して管理されます。データベースごとに管理ができないので注意してください。

 ロールの作成と設定は以下の手順で行います。

データベース(テーブルなどを含む)
↓
ロールの作成
	ロール名
↓
ロールの設定
	データベース作成許可、ロールの追加許可、PostgreSQLへのログイン、パスワード設定
↓
データベースやテーブルなどにロールを設定

 ロールの管理はスーパーユーザーかロールの追加権限のあるユーザーで行います。
初期状態では「postgres」ユーザーがスーパーユーザーです。
(「postgres」というユーザー名は知られている名称ですので公のサーバーでの使用は好ましくありません。)

ロールの一覧

  psqlにログインし、\duコマンドを実行します。

\duコマンド ロールの一覧
$ psql
...

=# \du

テーブルごとのロール設定

 テーブルごとのロールの設定を確認することができます。\dpコマンドでテーブルを指定します。

テーブルごとのロール設定
=# \dp table_name

 詳しくは「PostgreSQLユーザー(ロール)管理」を参照してください。

 外部からPostgreSQLのデータ操作をするには、PostgreSQLへのログイン認証、ロールの設定、テーブルごとのロール設定が必要になります。
 ログイン認証、ロールの設定、テーブルごとの設定については「ログイン認証とロールの設定」を参照して下さい。

データベースの管理(確認、作成、接続、削除)

 データベースの管理としては、既存データベースの一覧で確認、データベースの作成、削除、オーナー管理、ディレクトリ管理があります。ここでは簡単に説明します。

 詳しくは、データベースの管理(一覧、作成、接続、削除)を参照して下さい。

データベースの一覧確認

$ psql -l

データベースの作成

 まず最初にデータを統括するためのデータベースを作成しなければなりません。データベースにデータを収めるためのテーブルやビュー、トリガといったスクリプトなどが入ります。

# CREATE DATABASE new_database OWNER owner_name;

 ログイン可能なユーザーを追加し、そのユーザーでpsqlコマンドを実行するとデータベースがないというエラーが返ってくることがあります。 そのような場合は、ユーザー名のデータベースを作ります。

# CREATE DATABASE user_name OWNER user_name;

 詳しくは「データベースの管理(一覧、作成、接続、削除)」を参照してください。

テーブル定義

 テーブルとは、データベースにおいてデータを格納するために用意する表のようなものです。

列名の部分

テーブルの作成

 テーブルの作成は以下のように CREATE TABLE クエリーで定義します。

 表計算のような行列の概念があり、CREATE TABLEでは列の各カラム(項目)の定義をします。

テーブルの作成
CREATE TABLE table_name(
	id	SERIAL PRIMARY KEY,
	name	VARCHAR( 30 )
);

 データ型には主に以下のものがあります。

データ型

 PostgreSQL で扱うデータ型には主に文字列型、数値型、日時型があります。データ型はカラムのデータタイプ定義にも利用します。

文字列型

 CHAR() も VARCHAR() も指定文字数を超えて入力しようとするとエラーになります。

文字列型
タイプ名概要
CHAR()固定長。10,485,760 文字まで。指定数に満たない文は空白で埋められる。別名に character(n) がある。
VARCHAR()可変長。10,485,760 文字まで。指定数に収まるように保存領域が変化する。別名に character varying() がある
TEXT1G 分の文字まで

 文字数が決まっているような場合は CHAR() または VARCHAR()を。10,485,760 文字を超えるような場合や文字数が決まっていない場合は TEXT を使用するとよいでしょう。

 CHAR() は固定長で指定された文字数の容量を確保します。もしも指定文字数に満たない場合は空白で埋めます。
 VARCHAR() は可変長で保存される文字数によって確保される容量が変わります。保存領域を無駄にしたくない場合は VARCHAR() を使用した方がよいでしょう。

 入力時に文字列を扱うには引用符'または"で囲みます。

数値型

 数値を表すには以下のようなデータタイプを使用します。

 浮動小数点には DECIMAL や NUMERIC を使う必要があります。DECIMAL や NUMERIC は別名で NUMERIC の方が多く使用されるようです。

数値型
タイプ名概要
SMALLINTSIGNED、-32,768 〜 32767
INT, INTEGERSIGNED、-2,147,483,648 〜 2,147,483,647
BIGINTSIGNED、-9,223,372,036,854,775,808 〜 9,223,372,036,854,775,807
DECIMAL整数は 131,072 桁、小数点以降は 16,383 桁
NUMERIC整数は 131,072 桁、小数点以降は 16,383 桁
REAL可変精度。6 桁精度
DOUBLE PRECISION可変精度。15 桁精度
連番型
SMALLSERIAL自動増分。1 ~ 32,767
SERIAL自動増分。1 ~ 2,147,483,647
BIGSERIAL自動増分。1 ~ 9,223,372,036,854,775,807

 整数の場合は、INT を使用するようにします。メモリに制限がある場合は SMALLINT。INT ではまかえない場合は BIGINT を使用します。

 小数点以下が必要で、金額といった正確性を求められる場合は浮動小数点が扱える NUMERIC を使用します。DECIMAL や NUMERIC は別名です。
 REAL や DOUBLE PRECISION も浮動小数点が扱えますが丸込みが起きるために不正確です。

日時型

 日付や時刻には DATE や TIMESTAMP、TIME といったデータ型が用意されています。

日時型
タイプ名概要
DATEYYYY-MM-DD。4713 BC 〜 5874897 AD
TIME [ WITHOUT TIME ZONE ]HH:MI:SS。00:00:00 〜 24:00:00
TIME WITH TIME ZONEHH:MI:SS。時間帯指定あり。00:00:00+1459 〜 24:00:00-1459
TIMESTAMP [ WITHOUT TIME ZONE ]YYYY-MM-DD HH:MI:SS。4713 BC 〜 294276 AD
TIMESTAMP WITH TIME ZONEYYYY-MM-DD HH:MI:SS。時間帯指定あり。4713 BC 〜 294276 AD
INTERVAL時間間隔。1μ秒単位。-178,000,000 年 〜 178,000,000 年

テーブル一覧

 テーブル一覧は \dt コマンドを使用します。

PostgreSQL テーブル一覧
postgres=# \dt

テーブルの内容確認

 テーブルの内容確認は \dt コマンドでテーブル名を指定します。

PostgreSQL テーブル内容
postgres=# \dt table_name

 テーブルの操作に関して詳しくはテーブルを参照してください。

SQLファイルの読み込み

 SQLファイルを、PostgreSQLのコマンドラインインターフェイスから読み込み、実行することができます。

database=> \i DataBase/sql_file.sql

 読み込むsqlファイルはカレントディレクトリからファイル名で辿ります。読み込み先のデータベース名は確認するようにします。

 詳しくはSQLファイルの読み込みと実行を参照してください。

PostgreSQLの自動起動(デーモンプロセス設定)

 Ubuntu のパッケージから PostgreSQL をインストールしたら、標準ではコンピュータの起動時に PostgreSQL も起動します。
 もしも PostgreSQL を自動で起動しないように設定するには、 PostgreSQL の自動起動(デーモンプロセス設定)を参照してください。

データのエクスポートとインポート

 psqlコマンドプロントで利用する\COPYコマンドを使う方法があります。
 \COPYコマンドによってPostgreSQLデータベースのテーブルレコードの出力と取り込みをすることができます。

詳しくはデータのエクスポートとインポートについてを参照してください。

その他

 PostgreSQL を運用する上での Tips です。

日本語のソート
日本語をソートすると上手くいかない場合があります。多くの場合、LC_COLLATE の設定が英語になっています。その場合のソートの文字列の指定方法などについてです。