はじめてのPostgreSQLに役立つサイト集

pg_dump

# 出力形式(-F)をカスタム形式アーカイブ(c)でダンプ出力する
$ pg_dump -Fc -U {接続ユーザ名} {データベース名} > dumpname.dump

オプション	意味	参考サイト
--encoding={文字コード} -E {文字コード}	指定した文字セット符号化方式でダンプを作成します。	pg_dump - PostgreSQL 12.4文書
-F{フォーマット} --format={フォーマット}	出力形式を選択	pg_dump - PostgreSQL 13.1文書
--schema-only -s	データ定義(スキーマ)のみをダンプし、データはダンプしません。	pg_dump - PostgreSQL 12.4文書
-t {テーブル} --table={テーブル}	一致するテーブル（またはビュー、シーケンス、外部テーブル）のみをダンプします。	pg_dump - PostgreSQL 12.4文書

• ダンプファイルからデータベースを作り直す - Qiita

データ型

分類	型名	説明
文字型	character(n), char(n)	空白でパッドされた固定長文字列
文字型	character varying(n), varchar(n)	制限付き可変長文字列
文字型	text	制限無し可変長文字列
日付/時刻データ型	timestamp	日付と時刻両方
日付/時刻データ型	date	日付(時刻なし)
日付/時刻データ型	time	時刻(日付なし)

SQL

• 数値をlike検索する方法 - Qiita
• CASE文 - DBと仲良くしたいときに役立つリンク集
• SELECT
  • postgreSQLの健康状態をチェックする - 技术之旅
    • データベースやテーブルのサイズ、接続ユーザー数の確認方法が書かれています。
  • PostgeSQLにおけるIN句の２種類の使い方とNULLの注意 - Qiita
• UPDATE
  • PostgreSQL 8.0.4 文書 UPDATE

日時

-- 今日
select current_date;
-- 明日
select current_date + 1;
-- 今
select current_timestamp;
-- 5分前
select current_timestamp + '-5minutes';
-- timestamp型で「2017/04/19 0:00:00」
select to_timestamp('20170419','yyyyMMdd');

• PostgreSQL 日付の加算・減算を行う
• 9.8. データ型書式設定関数 - PostgreSQL 12.4文書

GRANT

/* データベースを操作するの権限を付与する */
GRANT ALL ON database {データベース名} TO {ロール名}

INSERT

insertはよくある書き方です。

INSERT INTO table_name [ ( column_name [, ...] ) ]
  { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
  [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

• PostgreSQL 9.4.5文書 INSERT
• レコードを挿入する【PostgreSQL】 - Programming Magic

いろんなものが見たいとき

テーブルの＊＊＊のが見たい

/* カラムの型とかを見たい */
select * from information_schema.columns where table_name like '%見たいテーブル名の一部%' and column_name like '%みたいカラム名一部%';
/* 主キーとか外部キーとかの制約がくっついているカラムを見たい */
select * from information_schema.constraint_column_usage where table_name like '%見たいテーブル名の一部%';
/* テーブルにくっついている制約を見たい */
select * from information_schema.table_constraints where table_name like '%見たいテーブル名の一部%' and constraint_type = '見たい制約(CHECK、FOREIGN KEY、PRIMARY KEY、UNIQUE)';
/* テーブル一覧を見たい */
select * from pg_stat_user_tables;
/* 全Viewの情報が見たい */
SELECT * FROM pg_views;
/* Viewの定義を見たい */
SELECT definition FROM pg_views WHERE viewname = 'foobar';
/* 外部テーブルを見たい */
select * from pg_class where relkind='f' and relname like '%テーブル名%';

テーブル	内容	参考サイト
pg_class	テーブルとその他に列を保有しているもの、あるいはテーブルに似た全てのものを目録にしています。	51.11. pg_class-PostgreSQL 12.4文書

データベースの＊＊＊が見たい

/* データベース一覧を見たい */
SELECT * FROM pg_database;
/* 最大接続数を見たい */
SHOW max_connections;

/* バージョンを見たい */
select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.15 on x86_64-unknown-linux-gnu, compiled by gcc .......
(1 row)

/* ユーザ一覧が見たい(パスワード見えない版) */
select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
 mana     |    16384 | t           | f        | f       | f            | ******** |          | 
(2 rows)
/* ユーザ一覧が見たい(パスワード見える版) */
select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |                                     |          | 
 mana     |    16384 | t           | f        | f       | f            | md5a6747d5690695f2d69c556af98aca23b |          | 
(2 rows)

/* 今使っているユーザ名を見たい */
select current_user;
 current_user 
--------------
 mana
(1 row)

よく使いそうなコマンドをメモ

よく使いそうなもの

# ログイン
$ psql -h hostname -U username -d databasename
$ psql -U username -d databasename

# ログアウト
$ \q

# データベースの切り替え
$ \c databasename

# 権限情報
$ \z
                            Access privileges
 Schema | Name | Type | Access privileges | Column privileges | Policies 
--------+------+------+-------------------+-------------------+----------
(0 rows)

# ユーザ一覧
$ \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 mana      | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

メタコマンド (DB内で使うやつ)	意味	参考リンク
\i {sqlファイル}	ファイルから入力を読み取り、キーボードから入力された場合と同じように実行	psql - PostgreSQL 13.1文書

同じ意味で違う書き方シリーズ

/* null検索※NULLとNULLとは"等しい"関係にはありませんので、expression = NULLと記述してはいけません。 */
expression IS NULL
expression ISNULL

• PostgreSQLからの切断 - PostgreSQLへの接続と切断 - PostgreSQLの使い方
• PostgreSQLのバージョンを調べるには
• hacknote PostgreSQLのバージョン確認方法
• 9.2. 比較関数および演算子
• information_schema
  • PostgreSQL: テーブルの主キーを列順に取得する - log.nissuk.info
  • 35.50. table_constraints
  • 35.17. constraint_column_usage
  • 35.16. columns
• PostgreSQLのシステムテーブル入門　（暗記用のSQL集） - 主に言語とシステム開発に関して
• PostgreSQL 外部キー一覧の取得 - Qiita
• Postgresql(psql)でViewビューの定義を調べる方法 · DQNEO起業日記
• SHOW
  • PostgreSQL: Documentation: 9.6: SHOW
  • configuration - Query a parameter (postgresql.conf setting) like "max_connections" - Stack Overflow

テーブルとカラムのcommentが見たい

• PostgreSQLにてテーブルやカラムの各種情報を取得するSQL (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得) - いろいろ備忘録日記

OracleのSQLを書き換えたい

• OracleのSQLをPostgeSQLのSQLにしてみる時に役立てたいリンク集 - Qiita

インストール

• MacにHomebrewでPostgreSQLをインストールしてデータベースを作る - ponsuke_tarou’s blog
• PostgreSQLのpsqlをインストールする - ponsuke_tarou’s blog

ポート設定

• デフォルトは5432です。
• Hatena::Diary (=゜ω゜)ノぃょぅ　にっき PostgreSQLのポート番号を変更する

起動・停止・再起動

• PostgreSQL の起動と停止

予約語

• SQLキーワード

クライアントツール

• Psequel
  • macOS High SierraにHomebrew CaskでPostgreSQLクライアントのPSequelをインストールする - Qiita
• pgAdminIII
  • PostgreSQL 9.2にクライアントから接続する pgAdminのインストール設定 | ＩＴ関連の技術情報サイト
  • pgAdminIIIで楽々PostgreSQL [データベース] All About

困った

• org.postgresql.util.PSQLException:
  • ERROR: permission denied for relation
    • アクセス権限を確認しましょう。
    • 権限の追加 - ロール(ユーザー)の作成 - PostgreSQLの使い方
  • ERROR: relation "テーブル名" does not exist
    • テーブルの存在を確認しましょう。
• ERROR: current transaction is aborted, commands ignored until end of transaction block
  • ロールバックしましょう。
  • Hatena iakioの日記 エラーの後にロールバックしなければいけない理由