PostgreSQL 基本的な使い方
PostgreSQLのインストールや、基本的な使い方や関数の使用例などを記述してます。ページ下部には、プログラム言語からPostgreSQLに接続して使用するサンプルコードやエラー対応法もあります。
PostgreSQLとは
オープンソースのリレーショナルデータベース管理システムで、InstagramやSkypeやAppleなどにも利用され、世界中で使用されています。
環境構築
環境の設定や構築に関連することをまとめてます。インストールしたPostgreSQLを使用するまでの手順となります。
- centos9 PostgreSQL15をインストールする
- centos9 PostgreSQL 14をインストールする手順
- centos8 PostgreSQL 14をインストールする手順
- Rocky Linux PostgreSQL 14をインストールして使用する
- MIRACLE LINUX PostgreSQL 14をインストールする手順
- AlmaLinux PostgreSQL 13をインストールする
- Linux postgresqlを停止する
- PostgreSQLをアンイストールする
ツール系
PostgreSQLをGUIで操作するツール「pgadmin」などについてまとめてます。
- CentOS8 pgAdmin4をインストールして使ってみる
- Ubuntu22.04 「pgAdmin4」をインストールする
- Azure Data Studio PostgreSQLに接続する
- pgadmin 日本語化を行う
- pgadmin4 TEMPORARY TABLE(一時テーブル)を表示する
コマンド系
シェルコマンドに関することをまとめてます。
ユーザー操作
ユーザーに関連することを、まとめてます。
レコードやデータ操作
実際にレコードを操作するときに、使用する関数やクエリなどをまとめてます。
基本
- DBを作成する
- DBを削除する
- DB一覧を確認する
- 現在のDB名を取得する
- カラムを追加する
- カラム名を変更する
- テーブルのカラムの数をカウントする
- テーブル名を一覧で取得する
- テーブルを作成する
- テーブルのサイズを一覧で取得する
- テーブルのカラムを一覧で取得する
- VIEWを作成する
- viewの一覧を取得する
- 範囲を指定して連続した値を生成する
- case文で結果に条件を設定する
- テーブルに設定されているインデックスを確認する
- インデックスを追加する
- indexを作成する
- indexの一覧を取得する
- プライマリキーを削除する
- 値を比較して等しい場合はnullを返す
- マテリアライズドビューを作成する
- マテリアライズドビューを削除する
- TEMPORARY TABLE(一時テーブル)を作成する
- REINDEXを実行する
- 合計を行に出力する
- 取得する位置を指定する
- シーケンスを作成する
- シーケンスの一覧を取得する
- データ型を取得する
- データベースのサイズを一覧で取得する
- レンジパーティションを作成する
- nullだった場合は置換する
- 大量のデータを作成する
- タイムゾーンを確認する
- タイムゾーンを変更する
- 設定できるタイムゾーンを一覧で取得する
- オートインクリメント値を初期化する
- スロークエリのログを取得できるように設定する
- 複数行をコメントアウト
文字データ
- 文字列内でシングルクオーテーションを使用する
- 文字列を置換する
- 文字数をカウントする
- 文字列を1文字ずつ置換する
- 文字列を反転させて表示する
- 文字列を結合する
- 文字列の文字数を取得する
- 文字列の前後にある半角スペースを除く
- 区切り位置からの順番を指定して文字列を表示する
- 文字列を日時(timestamp型)に変換する
- asciiコードを取得する
- Unicodeコードポイントから文字を生成する
- 文字列を日付に変換する
- 文字列を数値に変換する
- 先頭の文字だけ大文字に変換する
- 前に指定した文字数だけ文字を埋める
- 最初から指定した文字数だけ取得する
- 最後から指定した文字数だけ取得する
- 文字列のビット数を取得する
- 指定した位置から指定した文字数だけ取得する
- 半角スペースを全て除く
- 指定した文字列の位置を取得する
- 文字のバイト数を取得する
- 文字列の前にある半角スペースを取り除く
- 文字列の後ろにある半角スペースを取り除く
- 文字列を指定した回数繰り返す
- 文字列の前後から指定した文字列を削除する
- to_char使用時に空白が入る
- ランダムなひらがなを生成する
- アルファベットの小文字を大文字に変換する
- アルファベットの大文字を小文字に変換する
- 区切り文字を指定して文字列を結合する
- 後ろに指定した文字数だけ文字を埋める
- 文字列からmd5値を取得する
- 正規表現に一致した文字列を置換する
- 改行を削除する
- エスケープされたUnicode文字を文字列に変換する
数値
- 数値を文字列に変換する
- 数値の前にプラス表記をつける
- 余りを求める
- 割り算の結果を取得する
- ランダムな値を生成する
- 小数以下の切り捨てを行う
- 序数表記に変更する
- 現在の日時を取得する
- ラジアンを求める
- ラジアン数から角度を計算する
- 小数部の桁数を取得する
- 0埋めを行う
- 16進数に変換する
- 面積を求める
- 直径の値を求める
- 立方根を求める
- 絶対値を取得する
- 少数以下の桁数を取得する
- 少数末尾にある0を削除する
- 中央値を取得する
- 中心点の値を求める
- 自然対数の底(e)の累乗を取得する
- 累乗を指定して計算結果を取得する
- sin値を取得する
- tan値を取得する
- 余接(コタンジェント)を取得する
- 双曲線正弦(ハイパーボリックサイン)を取得する
- 双曲線余弦(ハイパーボリックコサイン)を取得する
- 双曲線正接(ハイパーボリックタンジェント)を取得する
- 逆双曲線逆余弦 (アークハイパーボリックサイン)を取得する
- 逆双曲線余弦(アークハイパーボリックコサイン)を取得する
- 逆双曲線正接 (アークハイパーボリックタンジェント)を取得する
日付
- 時間の足し算を行う
- 現在日付を取得する
- 日付の差分を求める
- 日付の重複をチェックする
- 現在の日付と時刻をフォーマットされた文字列で返す
- 日付のフォーマットを設定する
- 日や月の足し算を行う
- 月をローマ数字で取得する
- 日付や時間の切り捨てを実行する
- 現在時刻を取得する
- 生年月日から年齢を確認する
- 時間から日数を計算する
- 年、月、日、時、分、秒から時刻を作成する
- 時分秒から時刻を作成する
- 日付を序数表記に変更する
- 月末の日付を求める
- 月を英単語で取得する
- 日数から月を計算する
- 英表記の月から数値に変換する
- 何世紀であるかを表示する
- 日月日から日付を作成する
- 年月週日時分秒から日時を作成する
- 時間の表示を符号により調整する
- 有限の日付であるかを判定する
- 生年月日から年齢を取得する
- 実行時に変化する現在日付と時刻を取得する
配列
- 配列内の要素を行として表示する
- 配列内の要素を行として表示する
- 配列から指定した値を除去する
- 配列の先頭に値を追加する
- 配列の数をカウントする
- 配列内の要素の数を取得する
- 配列を同じ値で初期化する
- 配列の指定した値を置換する
- 配列の次元数を取得する
- 配列に配列を追加する
- 配列の最後に値を追加する
- 配列のデータを指定した区切り文字で文字列化する
- 配列の次元とデータ数を取得する
- 指定した値が配列のどこにあるかを取得する
- 指定した区切り文字で配列を作成する
- where句に配列を使用する
- 区切る単位を正規表現で指定して文字列から配列を作成する
- 指定したデータで配列を生成する
ネットワーク系
その他
- 待機時間を作成する
- copy toで特定のカラムのみデータを出力する
- ENUM値(列挙型)の値を確認する
- ENUM値(列挙型)を使用する
- ファイルを取り込んでデータを作成する
- スラッシュ表記のみを抽出する
- 指定した時間に実行する
- intervalを使って待機時間をつくる
- UUIDをカラムの型として使用する
- 利用可能な照合リストを取得する
- テーブルのデータをファイルに書き出す
- バージョンを確認する
- json形式のデータに変換する
- jsonデータに値を追加する
- json値の型を取得する
- json配列内で条件に一致した最初の値を取得する
- json配列からインデックス番号を指定して値を取得する
- json配列内で条件を満たしている値を取得する
- jsonデータからnullを除去する
- jsonを整形する
データベース管理
データベース管理/保守するときに、必要なものをまとめてます。
- 論理バックアップを行う
- 指定したDBのセッションを切断する
- クライアントのエンコーディングを確認・設定する
- 現在のスキーマを取得する
- テーブルにコメント追加する
- 現在のセッションのサーバー側のプロセスIDを取得する
- 接続中のセッションを確認する
- 現在のトランザクションIDを取得する
- DBサーバーのIPアドレスを取得する
- セッションを切断する
- クライアントで使用しているポート番号を取得する
- DBサーバーで使用しているポート番号を取得する
- 設定ファイルの最終取得時間を取得する
- サーバーの起動時刻を取得する
- 利用している拡張機能を確認する
- Windows postgresのパスワードをpgadminから変更する
プログラムから使用
プログラム言語からPostgreSQLを操作する方法をまとめてます。
- node.js PostgreSQLに接続する
- node.js PostgreSQLにselectを実行してデータを取得する
- node.js PostgreSQLにselectをasync/awaitで実行する
- node.js PostgreSQLにinsertを行う
- node.js PostgreSQLにトランザクションを持たせてクエリを実行する
- rails6 Postgresqlを使用するまでの手順
docker
dockerを使用したPostgreSQLに関連する情報をまとめてます。
- docker PostgreSQL バックアップを実行する
- docker PostgreSQL バックアップをcronで定期的に実行する
- dockerでrails6とpostgresql環境を構築する
- docker composeを使ってPostgreSQLを構築する
エラー対応
エラーが発生した際の、対応についてまとめてます。
- エラー「Killing process xxxx(postmaster) with signal SIGKILL.」がでて起動しない場合の対処法
- エラー「ERROR: 演算子が存在しません: text = integer」が発生した場合の対処法
- エラー「ERROR: 関数to_json(xxx,xxx)は存在しません」が発生した場合の対処法
- PostgreSQL 14 「ERROR: 機能拡張の制御ファイル/usr/pgsql-14/share/extension/pgcrypto.controlをオープンできませんでした」が発生した場合の対処法
- 「DETAIL: 他にこのデータベースを使っている 1 個のセッションがあります。」が発生した場合の対処法
- エラー「FATAL: データディレクトリ”/var/lib/pgsql/data”の権限設定が不正です」が発生した場合の対処法
- pg_dump実行時に「FATAL: ユーザ “xxx” で対向(peer)認証に失敗しました」が発生した場合の対応
- 起動時にエラー「”/var/lib/pgsql/12/data/” is missing or empty.」が発生した際の対応方法
- 起動時にエラー「”/var/lib/pgsql/11/data/” is missing or empty.」が発生した場合の対処法
- 起動時にエラー「FATAL: data directory “/xxx” has invalid permissions」が発生した場合の対処法
- pg_dump時 「サーババージョンの不整合のため処理を中断しています」が発生した場合の対処法