はじめてのPostgreSQLに役立つサイト集 ref: https://qiita.com/ponsuke0531/items/12bf73640ee54c238e7e

file0.sql

-- 今日
select current_date;
-- 明日
select current_date + 1;
-- 今
select current_timestamp;
-- 5分前
select current_timestamp + '-5minutes';

file0.txt

-- ログイン
=> 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)

-- バージョン確認
=> select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.15 on x86_64-unknown-linux-gnu, compiled by gcc .......
(1 row)

file1.sql

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

file1.txt

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

file2.txt

\q

file3.txt

\c databasename

file4.txt

\z

file5.sql

-- 一般的なCASE形式
CASE WHEN {条件式} THEN {条件式がtrueの時の結果}
     [WHEN ...]
     ELSE {条件に当てはまらない時の結果}
END
-- 単純なCASE形式
CASE {条件となるカラム}
    WHEN {値} THEN {カラム値が値に一致した時の結果}
    [WHEN ...]
    ELSE {値に一致しない時の結果}
END

file5.txt

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

information_schema

-- カラム一覧
SELECT * FROM information_schema.columns WHERE table_name = {テーブル名};
-- テーブル一覧
SELECT * FROM pg_stat_user_tables;

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

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

insertはよくある書き方で大丈夫みたいです。

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

null判定

CASE WHEN {null判定したいカラム} IS NULL THEN {nullの場合の結果} ELSE {nullでない場合の結果} END

OracleのDECODEをPostgreqslのCASEで書く

-- Oracle
DECODE(kokyaku_rank, 1, '優', 2,'良', 3,'可', '不可')
-- Postgresql
CASE kokyaku_rank WHEN 1 THEN '優' WHEN 2 THEN '良' WHEN 3 THEN '可' ELSE '不可' END

Oracle関数をPostgreqsl関数にする

/*
 * 今！現在日時的なもの
 */
-- Oracle
SYSDATE;
SYSTIMESTAMP;
-- PostgreSQL
CURRENT_DATE;
CURRENT_TIMESTAMP;
/*
 * 文字列結合
 */
-- Oracle：連結演算子
SELECT 'Name is ' || last_name FROM employees ORDER BY last_name;
-- PostgreSQL：連結演算子というかは不明だが同じものがある
SELECT 'Name is ' || last_name FROM employees ORDER BY last_name;
/*
 * DECODE >>> CASE
 */
-- Oracle
DECODE('判定する値', 1, '1だった時の値', 2, '2だった時の値', '1でも2でもない場合')
-- Postgresql
CASE '判定する値' WHEN 1 THEN '1だった時の値' WHEN 2 THEN '2だった時の値' ELSE '1でも2でもない場合' END
/*
 * NVL >>> COALESCE
 */
-- Oracle
NVL('nullかもしれない値', 'nullだった時の値')
-- Postgresql
COALESCE('nullかもしれない値', 'nullだった時の値')
/*
 * SYSDATE >> current_date(フォーマットは違うよ♥)
 */
-- Oracle
select SYSDATE;
-- PostgreSQL
select current_date;

Sample

/* 同じ内容を2つの形式で書いてみる */
CASE WHEN col = 1 THEN 'hoge' WHEN col = 2 THEN 'fuga' ELSE 'pon' END;
CASE col WHEN 1 THEN 'hoge' WHEN 2 THEN 'fuga' ELSE 'pon' END;
/* こういうのは単純形式では書けない */
CASE WHEN col IN (1,2,3) THEN 'hoge' WHEN col IN (4,5,6) THEN 'fuga' ELSE 'pon' END;
CASE WHEN col1 <> 1 AND col2 = 'a' THEN 'hoge' WHEN col1 = 1 AND col2 = 'b' THEN 'fuga' ELSE 'pon' END;

いろんなものが見たい

/* カラムの型とかを見たい */
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_database;
/* テーブル一覧を見たい */
select * from pg_stat_user_tables;
/* 全Viewの情報が見たい */
SELECT * FROM pg_views;
/* Viewの定義を見たい */
SELECT definition FROM pg_views WHERE viewname = 'foobar';
/* 最大接続数を見たい */
SHOW max_connections;

/* バージョンを見たい */
select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.15 on x86_64-unknown-linux-gnu, compiled by gcc .......
(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 | {}

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

/* カラムの型とかを見たい */
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_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)

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

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

明示的な行ロック

/*
 * PstgreSQL
 */
-- トランザクションが終わるまで、他のトランザクションがロック、変更、削除できない
SELECT 〜 WHERE ... FOR UPDATE;
-- 同じ行のロックを獲得しようとするSELECT FOR KEY SHAREコマンドをブロックしないFOR UPDATE
SELECT 〜 WHERE ... FOR NO KEY UPDATE;
-- 排他ロックではなく共有ロックを獲得する
SELECT 〜 WHERE ... FOR SHARE;
-- SELECT FOR UPDATEはブロックされるが、SELECT FOR NO KEY UPDATEはブロックされないFOR SHARE
SELECT 〜 WHERE ... FOR KEY SHARE;

/*
 * Oracle
 */
-- SELECT対象の全ての表のレコードをロック
SELECT 〜 WHERE ... FOR UPDATE;
-- 表を結合する場合に使用。行ロックする表を指定
SELECT 〜 WHERE ... FOR UPDATE OF 行ロックする表.列名 [,column_name2...];
-- 対象レコードにロックがかかっていた時に、ロックが開放されるまで待つ
SELECT 〜 WHERE ... FOR UPDATE WAIT [待ち時間]
-- 対象レコードにロックがかかっていた時に、開放を待たずに下記エラーで復帰
SELECT 〜 WHERE ... FOR UPDATE NOWAIT;

構文

-- 検索形式
CASE WHEN {条件式} THEN {条件式がtrueになった時の結果}
     [WHEN ...]
     ELSE {どの条件式に当てはまらなかった時の結果}
END
-- 単純形式
CASE {条件となるカラム}
    WHEN {値} THEN {カラム値が値だった時の結果}
    [WHEN ...]
    ELSE {カラム値がどの値でもない時の結果}
END

構文の違いによる処理速度は変わらないらしいです。

-- 検索形式:こっちだと複数条件を組み合わせられます。
--  注意: CASEとWHENの間にカラム書いちゃダメ
CASE WHEN {条件式} THEN {条件式がtrueになった時の結果} [WHEN ...] ELSE {どの条件式に当てはまらなかった時の結果} END
-- 単純形式:値の一致だけが条件であればこちらがカンタンです。
CASE {条件となるカラム} WHEN {値} THEN {カラム値が値だった時の結果} [WHEN ...] ELSE {カラム値がどの値でもない時の結果} END