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


# pg_dump

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

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

- [ダンプファイルからデータベースを作り直す - Qiita](https://qiita.com/ponsuke0531/items/19a8bad493aa2860d7bc)


# データ型
|分類|型名|説明|
|:-:|:-:|:-:|
|[文字型](https://www.postgresql.jp/document/11/html/datatype-character.html)|character(n), char(n)|空白でパッドされた固定長文字列|
|[文字型](https://www.postgresql.jp/document/11/html/datatype-character.html)|character varying(n),<br/>varchar(n)|制限付き可変長文字列|
|[文字型](https://www.postgresql.jp/document/11/html/datatype-character.html)|text|制限無し可変長文字列|
|[日付/時刻データ型](https://www.postgresql.jp/document/12/html/datatype-datetime.html)|timestamp|日付と時刻両方|
|[日付/時刻データ型](https://www.postgresql.jp/document/12/html/datatype-datetime.html)|date|日付(時刻なし)|
|[日付/時刻データ型](https://www.postgresql.jp/document/12/html/datatype-datetime.html)|time|時刻(日付なし)|

# SQL
- [数値をlike検索する方法 - Qiita](https://qiita.com/chovin/items/cc5ab8d1b29c2557f10d)
- [CASE文 - DBと仲良くしたいときに役立つリンク集](https://qiita.com/ponsuke0531/items/f34d60e514dde47e81df#case)
- SELECT
    - [postgreSQLの健康状態をチェックする - 技术之旅](http://blog.51yd.org/post-169.html)
        - データベースやテーブルのサイズ、接続ユーザー数の確認方法が書かれています。
    - [PostgeSQLにおけるIN句の２種類の使い方とNULLの注意 - Qiita](https://qiita.com/ponsuke0531/items/fdfa8a4b1df8715917cb)
- UPDATE
    - [PostgreSQL 8.0.4 文書 UPDATE](https://www.postgresql.jp/document/8.0/html/sql-update.html)

## 日時
```sql
-- 今日
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 日付の加算・減算を行う](https://zukucode.com/2017/11/postgresql-date-add.html)
- [9.8. データ型書式設定関数 - PostgreSQL 12.4文書](https://www.postgresql.jp/document/12/html/functions-formatting.html)

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

## INSERT
```sql:insertはよくある書き方です。
INSERT INTO table_name [ ( column_name [, ...] ) ]
  { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
  [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
```

- [PostgreSQL 9.4.5文書 INSERT](https://www.postgresql.jp/document/9.4/html/sql-insert.html)
- [レコードを挿入する【PostgreSQL】 - Programming Magic](http://www.programming-magic.com/20071222002506/)



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

```sql:テーブルの＊＊＊のが見たい
/* カラムの型とかを見たい */
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文書](https://www.postgresql.jp/document/12/html/catalog-pg-class.html)|

```sql:データベースの＊＊＊が見たい
/* データベース一覧を見たい */
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)
```



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

```sh:よく使いそうなもの
# ログイン
$ 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 | {}
```

|メタコマンド<br/>(DB内で使うやつ)|意味|参考リンク|
|:--|:--|:--|
|\i {sqlファイル}|ファイルから入力を読み取り、キーボードから入力された場合と同じように実行|[psql - PostgreSQL 13.1文書](https://www.postgresql.jp/document/13/html/app-psql.html)|

```sql:同じ意味で違う書き方シリーズ
/* null検索※NULLとNULLとは"等しい"関係にはありませんので、expression = NULLと記述してはいけません。 */
expression IS NULL
expression ISNULL
```

- [PostgreSQLからの切断 - PostgreSQLへの接続と切断 - PostgreSQLの使い方](https://www.dbonline.jp/postgresql/connect/index3.html)
- [PostgreSQLのバージョンを調べるには](http://www.ksknet.net/postgresql/postgresql_2.html)
- [hacknote PostgreSQLのバージョン確認方法](http://hacknote.jp/archives/1574/)
- [9.2. 比較関数および演算子](https://www.postgresql.jp/document/9.6/html/functions-comparison.html)
- information_schema
    - [PostgreSQL: テーブルの主キーを列順に取得する - log.nissuk.info](http://log.nissuk.info/2014/03/blog-post.html)
    - [35.50. table_constraints](https://www.postgresql.jp/document/9.6/html/infoschema-table-constraints.html)
    - [35.17. constraint_column_usage](https://www.postgresql.jp/document/9.6/html/infoschema-constraint-column-usage.html)
    - [35.16. columns](https://www.postgresql.jp/document/9.6/html/infoschema-columns.html)
- [PostgreSQLのシステムテーブル入門　（暗記用のSQL集） - 主に言語とシステム開発に関して](http://language-and-engineering.hatenablog.jp/entry/20100220/p1)
- [PostgreSQL 外部キー一覧の取得 - Qiita](http://qiita.com/qs2006/items/049a4194556635e04d32)
- [Postgresql(psql)でViewビューの定義を調べる方法  &middot;  DQNEO起業日記](http://dqn.sakusakutto.jp/2011/05/postgresqlpsqlview.html)
- SHOW
    - [PostgreSQL: Documentation: 9.6: SHOW](https://www.postgresql.org/docs/current/static/sql-show.html)
    - [configuration - Query a parameter (postgresql.conf setting) like &quot;max_connections&quot; - Stack Overflow](http://stackoverflow.com/questions/8288823/query-a-parameter-postgresql-conf-setting-like-max-connections)

# テーブルとカラムのcommentが見たい
- [PostgreSQLにてテーブルやカラムの各種情報を取得するSQL (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得) - いろいろ備忘録日記](https://devlights.hatenablog.com/entry/20080226/p1)

# OracleのSQLを書き換えたい
- [OracleのSQLをPostgeSQLのSQLにしてみる時に役立てたいリンク集 - Qiita](https://qiita.com/ponsuke0531/items/7da0e916023ffcdc7a64)


# インストール
- [MacにHomebrewでPostgreSQLをインストールしてデータベースを作る - ponsuke_tarou’s blog](http://ponsuke-tarou.hatenablog.com/entry/2018/01/31/232012)
- [PostgreSQLのpsqlをインストールする - ponsuke_tarou’s blog](https://ponsuke-tarou.hatenablog.com/entry/2021/04/27/184056)

# ポート設定
- デフォルトは**5432**です。
- [Hatena::Diary (=゜ω゜)ノぃょぅ　にっき PostgreSQLのポート番号を変更する](http://d.hatena.ne.jp/nyanplus/20100910/1284079223)

# 起動・停止・再起動
- [PostgreSQL の起動と停止](http://www.turbolinux.co.jp/products/server/11s/user_guide/posgrestart.html)


# 予約語
- [SQLキーワード](https://www.postgresql.jp/document/8.4/html/sql-keywords-appendix.html)

# クライアントツール
- Psequel
    - [macOS High SierraにHomebrew CaskでPostgreSQLクライアントのPSequelをインストールする - Qiita](https://qiita.com/ponsuke0531/items/3f5ba801e24c38591165)
- pgAdminIII
    - [PostgreSQL 9.2にクライアントから接続する pgAdminのインストール設定 | ＩＴ関連の技術情報サイト](http://www.cyber-funnel.com/postgres/index890.html)
    - [pgAdminIIIで楽々PostgreSQL [データベース] All About](https://allabout.co.jp/gm/gc/47328/)



# 困った
- org.postgresql.util.PSQLException:
    - ERROR: permission denied for relation
        - アクセス権限を確認しましょう。
        - [権限の追加 - ロール(ユーザー)の作成 - PostgreSQLの使い方](https://www.dbonline.jp/postgresql/role/index3.html)
    - ERROR: relation "テーブル名" does not exist
        - テーブルの存在を確認しましょう。
- ERROR:  current transaction is aborted, commands ignored until end of transaction block
    - ロールバックしましょう。
    - [Hatena iakioの日記 エラーの後にロールバックしなければいけない理由](https://postgresql.g.hatena.ne.jp/iakio/20081120/1227202633)