PostgreSQL

PostgreSQL DCL(GRANT、REVOKE)

GRANT https://www.postgresql.jp/document/11/html/sql-grant.html GRANT { アクセス権の種類(SELECTなど) } ON [オブジェクト種別(TABLEの場合は省略可)] オブジェクト名 TO ユーザ名(またはロール名) [WITH GRANT OPTION]; アクセス権の種類 SELECTとCOPY…

PostgreSQL DML(SELECT、INSERT、UPDATE、DELETE、COPY)

SELECT https://www.postgresql.jp/document/11/html/sql-select.html SELECT 列名 FROM テーブル名 WHERE 検索条件 ORDER BY SELECT 列名 FROM テーブル名 WHERE 検索条件 ORDER BY ソート対象 [ ASC | DESC ] 返される行は指定した順番でソートされる。 LI…

PostgreSQL DDL(USER、TABLE、INDEX、SEQUENCE、SCHEMA、TABLESPACE)

ユーザ https://www.postgresql.jp/document/11/html/sql-createuser.html https://www.postgresql.jp/document/11/html/sql-dropuser.html https://www.postgresql.jp/document/11/html/sql-alteruser.html 作成 CREATE USER name [ [ WITH ] option [ ... …

PostgreSQL サーバアプリケーション(initdb、pg_ctl、pg_controldata)

initdb https://www.postgresql.jp/document/11/html/app-initdb.html initdb 例)initdb --pgdata=/usr/local/pgsql/data --encoding=UTF8 --locale=C -D、--pgdata データベースクラスタの格納先ディレクトリ -E、--encoding テンプレートデータベースの符…

PostgreSQL psql メタコマンド

psql \d テーブル名を指定すると、指定したテーブルの列名と列属性の一覧を表示する。2.05_Silverの例題解説「S2 運用管理 - S2.2 標準付属ツールの使い方」 \dp, \z テーブルやシーケンス、ビューの一覧を、各アクセス権の情報とともに取得する。 \copy \co…

PostgreSQL 一般知識、SQL基礎、データモデル

一般知識 https://www.postgresql.jp/document/11/html/multibyte.html カリフォルニア大学バークレー校での開発が元となっている。 PostgreSQL Global Development Group(PGDG)というコミュニティが開発したオープンソースデータベース。 メジャーバージョ…

PostgreSQL 運用管理(情報スキーマ、システムカタログ)

情報スキーマ https://www.postgresql.jp/document/11/html/information-schema.html SELECT * FROM information_schema.tables; データベース内のオブジェクトに関する様々な情報を提供する、information_schemaというスキーマ内のビューの集合として構成さ…

PostgreSQL データ型

文字列型 char(n), character(n) 文字数の上限を指定した固定長の文字列 varchar(n), character varying(n) 文字数の上限を指定した可変長の文字列 text 可変長の文字列 格納しようとした値の文字数が文字数制限のある型の指定以上の場合はエラーになる。 制…

PostgreSQL トリガー、ルール

トリガー https://www.postgresql.jp/document/11/html/sql-createtrigger.html https://www.postgresql.jp/document/11/html/sql-droptrigger.html https://www.postgresql.jp/document/11/html/sql-altertrigger.html 作成 CREATE TRIGGER トリガー名 { BE…

PostgreSQL VACUUM、VACUUM FULL、ANALYZE、自動バキューム

https://www.postgresql.jp/document/11/html/sql-vacuum.htmlデータの削除や更新によって不要になったデータ領域を回収する仕組み。 実行には、対象のテーブルやデータベースに対して所有者権限が必要である。 VACUUM VACUUM [FULL] [VERBOSE] [テーブル名]…

PostgreSQL 設定(postgresql.conf、SET、SHOW、ALTER SYSTEM、pg_hba.conf)

postgresql.conf https://www.postgresql.jp/document/11/html/config-setting.html https://www.postgresql.jp/document/11/html/runtime-config-logging.html 基本 大文字・小文字の区別はない。 デフォルトの単位はパラメータの種類によって異なる。 para…

PostgreSQL バックアップ(物理、pg_basebackup)

物理 物理バックアップの方が高速だが、サイズは大きい。(pg_dumpは低速だがサイズが小さい) ファイルのコピーなどでバックアップを作成する前に、必ずデータベースを停止する必要がある。 テーブルやデータベース単位でバックアップすることはできず、必…

PostgreSQL ビュー、マテリアライズドビュー

ビュー https://www.postgresql.jp/document/11/html/sql-createview.html https://www.postgresql.jp/document/11/html/sql-dropview.html https://www.postgresql.jp/document/11/html/sql-alterview.html 作成 CREATE VIEW ビュー名 [ ( 列名 ) ] AS SELE…

PostgreSQL バックアップリストア(PITR)

https://www.postgresql.jp/document/11/html/continuous-archiving.html 第8回 バックアップ・リストアデータベース全体のバックアップ(ベースバックアップ)に加えて、運用中のアーカイブとして記録された更新データを利用してデータベースをリカバリする…

PostgreSQL バックアップリストア(pg_dump、pg_dumpall、pg_restore)

pg_dump https://www.postgresql.jp/document/11/html/app-pgdump.html 第8回 バックアップ・リストア pg_dump [接続オプション] [-Fp|-Fc|-Fd|-Ft] [-f ファイル名] [データベース名] -Fp…平文(Plain)で出力する。-Fを省力した場合のデフォルト。 -Fc…カ…

PostgreSQL 演算子、関数、ファンクション、プロシージャ、プリペアド

文字列パターン一致 https://www.postgresql.jp/document/11/html/functions-matching.html 連結演算子 || LIKE演算子 判定対象のカラム名 LIKE 'パターン' DELETE FROM sample WHERE id LIKE '%A001%'; SIMILAR TO演算子 判定対象のカラム名 SIMILAR TO 'パ…

PostgreSQL クライアントアプリケーション(createuser、dropuser、createdb、psql、vacuumdb)

createuser https://www.postgresql.jp/document/11/html/app-createuser.html オプション -P, --pwprompt パスワードの設定 -s, --superuser スーパユーザ権限を付与 -d, --createdb データベース作成の権限を付与 -r, --createrole ユーザ(ロール)作成の権…

PostgreSQL トランザクション、カーソル、ロック

トランザクション 第7回 トランザクショントランザクション内でエラーが発生した場合、ROLLBACKコマンドが実行されるまで全てのSQLはエラーとなる。 トランザクション内で構文エラーが起きた場合は、トランザクションの全ての処理が取り消される。 自動コミ…

PostgreSQL データベースの正規化

https://oss-db.jp/dojo/dojo_info_04 第1正規形 主キーを設定する。 繰り返し現れる列のデータをグループ化して、別の表に切り離す。 導出項目(他の属性から算出できる項目)を削除する。 第2正規形 第一正規形の条件を満たす。 すべての非キー属性が候補キ…

PostgreSQL ユーザ管理

第2回 ユーザ管理とセキュリティ ユーザ操作 \du で登録ユーザの一覧が表示される。 Superuser "スーパーユーザ"はデータベース内のアクセス制限をすべて変更することができます。 Create role 新しいロール(=ユーザ)を作成できることを示します。 Create DB…