データの参照
INSERT / UPDATE / DELETE
- INSERT
- UPDATE
- DELETE
データ型

データの参照

SELECT文

文字の扱いについて

キーワードの大文字と小文字は区別しない

公式/キーワード
- SELECTとか
- いっぱいある

文字や日付情報はシングルクォートで囲む

COPY文はシングルクォートが必要
- cf. \copyメタコマンドは不要

列(カラム)、テーブル、検索条件の指定

Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

URL: https://www.postgresql.org/docs/12/sql-select.html

ORDER BY

ORDER BY 1 とかできたりする
- select listの1つめ
- 【所感】やめたほうがいいと思う
【補】USING operator
- 公式ドキュメント
- ASC は USING <
- DESC は USING >とふつうは同義
- ただし、演算子をいじれるのでこの限りではない

LIMITとOFFSET

LIMIT { count | ALL }
OFFSET start

公式
【補】
- countがNULLだとLIMIT ALLと同義
  - no limit
- startがNULLだとOFFSET 0と同義

DISTINCT

公式
【補】DISTINCTじゃないやつ: SELECT ALL
- ふだん使ってるSELECT
DISTINCT ON ( expression [, ...] )
- 特定のカラムについて重複排除とかできる
- 【補】式を指定できるので、特性関数を噛ませて重複除去もできる
ONを省略するとselect listの全カラムを用いて重複除去
【補】DISTINCT ON試してみる

postgres=# SELECT * FROM alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)

DISTINCT ONすると行の順番が変わる

postgres=# SELECT DISTINCT * FROM alphabets;

 id | ch 
----+----
  3 | C
  1 | A
  2 | B
  4 | D
  5 | E
  6 | F
(6 rows)

mod(id, 3)について重複除去してみる

postgres=# SELECT DISTINCT ON (mod(id, 3)) * FROM alphabets;

 id | ch 
----+----
  3 | C
  1 | A
  2 | B
(3 rows)

GROUP BYとHAVING

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
...

WHERE, GROUP BY, HAVINGはこの順番に評価される

副問合せ

普通のサブクエリはとくに面白くないので略
【補】WITHクエリ
公式

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

ネスト回避

INとNOT IN

【補】例: SETで書き換え可能な設定を列挙する

postgres=# SELECT name, context FROM pg_settings WHERE context IN('user', 'superuser');

                name                 |  context  
-------------------------------------+-----------
 application_name                    | user
 array_nulls                         | user
 backend_flush_after                 | user
 backslash_quote                     | user
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 commit_delay                        | superuser
 commit_siblings                     | user
 constraint_exclusion                | user
 cpu_index_tuple_cost                | user
 cpu_operator_cost                   | user
 cpu_tuple_cost                      | user
 cursor_tuple_fraction               | user
 DateStyle                           | user
...

【補】NULLが交じるとNULLになることに留意する

ANY

特徴
- 副問合せ部分はかならず1列であること
  - 値を指定する場合は配列形式
- 演算子を使える
【補】例: 'transaction'と名のつくコンフィグと同じcontextのコンフィグを列挙する
'transaction'と名のつくコンフィグ一覧

postgres=# SELECT name, context FROM pg_settings WHERE name LIKE '%transaction%';

                name                 |  context   
-------------------------------------+------------
 default_transaction_deferrable      | user
 default_transaction_isolation       | user
 default_transaction_read_only       | user
 idle_in_transaction_session_timeout | user
 log_transaction_sample_rate         | superuser
 max_locks_per_transaction           | postmaster
 max_pred_locks_per_transaction      | postmaster
 max_prepared_transactions           | postmaster
 transaction_deferrable              | user
 transaction_isolation               | user
 transaction_read_only               | user
(11 rows)

このいずれか(any)と同じcontextのコンフィグ

postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(SELECT context FROM pg_settings WHERE name LIKE '%transaction%')

                name                 |  context   
-------------------------------------+------------
 allow_system_table_mods             | postmaster
 application_name                    | user
 archive_mode                        | postmaster
 array_nulls                         | user
 autovacuum_freeze_max_age           | postmaster
 autovacuum_max_workers              | postmaster
 autovacuum_multixact_freeze_max_age | postmaster
 backend_flush_after                 | user
 backslash_quote                     | user
 bonjour                             | postmaster
 bonjour_name                        | postmaster
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 cluster_name                        | postmaster
...

副問合せ部分が2カラム以上だとエラー

postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(SELECT name, context FROM pg_settings WHERE name LIKE '%transaction%');

ERROR:  subquery has too many columns
LINE 1: ...LECT name, context FROM pg_settings WHERE context = ANY(SELE...

値で指定するときは配列形式
- ARRAY[...]

postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(ARRAY['user', 'superuser', 'postmaster']);

                name                 |  context   
-------------------------------------+------------
 allow_system_table_mods             | postmaster
 application_name                    | user
 archive_mode                        | postmaster
 array_nulls                         | user
 autovacuum_freeze_max_age           | postmaster
 autovacuum_max_workers              | postmaster
 autovacuum_multixact_freeze_max_age | postmaster
 backend_flush_after                 | user
 backslash_quote                     | user
 bonjour                             | postmaster
 bonjour_name                        | postmaster
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 cluster_name                        | postmaster
...

BETWEEN

BETWEEN A AND B
A < Bの場合、単にFALSEに評価される

結合

INNER JOIN

INNER省略可
ONで結合条件指定

postgres=# SELECT * FROM numbers JOIN alphabets ON numbers.id = alphabets.id;

 id | num | id | ch 
----+-----+----+----
  1 |   1 |  1 | A
  2 |   2 |  2 | B
  3 |   3 |  3 | C
(3 rows)

【補】結合条件ON TRUEはCROSS JOIN相当

postgres=# SELECT * FROM numbers n1 JOIN numbers n2 ON TRUE;

 id | num | id | num 
----+-----+----+-----
  1 |   1 |  1 |   1
  1 |   1 |  2 |   2
  1 |   1 |  3 |   3
  2 |   2 |  1 |   1
  2 |   2 |  2 |   2
  2 |   2 |  3 |   3
  3 |   3 |  1 |   1
  3 |   3 |  2 |   2
  3 |   3 |  3 |   3
(9 rows)

USING ( join_column [, ...] ) で結合カラム名指定
- select listを*とした場合、USINGで結合すると、結合カラムは1つにまとまる
  - cf. ONだと両テーブルのカラムが出てくる

postgres=# select * from numbers join alphabets using (id);

 id | num | ch 
----+-----+----
  1 |   1 | A
  2 |   2 | B
  3 |   3 | C
(3 rows)

NATURAL
- USINGのショートハンド
- 同名のカラム自動検出
- 【補】なければON TRUE相当(直積になる)

postgres=# SELECT * FROM numbers NATURAL JOIN alphabets;

 id | num | ch 
----+-----+----
  1 |   1 | A
  2 |   2 | B
  3 |   3 | C
(3 rows)

CROSS JOIN

直積
FROM複数指定でも

postgres=# SELECT * FROM numbers n1, numbers n2;

 id | num | id | num 
----+-----+----+-----
  1 |   1 |  1 |   1
  1 |   1 |  2 |   2
  1 |   1 |  3 |   3
  2 |   2 |  1 |   1
  2 |   2 |  2 |   2
  2 |   2 |  3 |   3
  3 |   3 |  1 |   1
  3 |   3 |  2 |   2
  3 |   3 |  3 |   3
(9 rows)

OUTER JOIN

OUTER省略可能
LEFT, RIGHT, FULLが使える

EXISTSとNOT EXISTS

【補】必ずTRUE, FALSEのいずれかを返す
- NULLは返さない

UNION / EXCEPT / INTERSECT

集合演算
公式/UNION
- 和集合
公式/INTERSECT
- 積集合(共通部分)
公式/EXCEPT
- 差集合
UNIONの一般形

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statementとは、下記を含まないSELECT文
- カッコで囲まれていないORDER BY, LIMIT
  - 囲まれていれば含められる
  - 囲まれていない場合、UNION結果にLIMIT, ORDER BYが適用される
- ロック
  - 公式
  - FOR NO KEY UPDATE
  - FOR UPDATE
  - FOR SHARE
  - FOR KEY SHARE
デフォルトDISTINCT
- 重複行を除去する
- ので、ソートが走る
INTERSECT, EXCEPTも同様
優先順位
- 積 > 和 = 差
- 左 > 右

INSERT / UPDATE / DELETE

INSERT

Command:     INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

URL: https://www.postgresql.org/docs/12/sql-insert.html

公式
例

postgres=# SELECT * FROM alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
(3 rows)

postgres=# INSERT INTO alphabets (id, ch) VALUES (4, 'D'),(5, 'E'),(6, 'F') RETURNING *;

 id | ch 
----+----
  4 | D
  5 | E
  6 | F
(3 rows)

INSERT 0 3

postgres=# select * from alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)

SELECT文のresult setも流し込める

postgres=# CREATE TABLE alphabets2 (id integer, ch char(1));
CREATE TABLE

postgres=# INSERT INTO alphabets2 SELECT * FROM alphabets;
INSERT 0 6

postgres=# SELECT * FROM alphabets2;
 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)

【補】ON CONFLICT DO UPDATEでUPSERTできる

UPDATE

Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-update.html

公式
SELECTのresult setも流し込める
【補】UPDATE ONLY
- ONLY指定: 指定のテーブルのみ更新
- ONLY指定なし: 指定のテーブルを継承した派生テーブルも更新
  - PostgreSQLはオブジェクト指向リレーショナルデータベース

DELETE

全行消すときはTRUNCATEを使おうね
- 公式
- 高速
- 【補】即座にディスク領域が回収される
  - VACUUMを手動で実行する必要がない
- 【補】D権限を要する

データ型

数値

公式

型名	種類	サイズ/バイト
smallint	整数	2
integer, int	整数	4
bigint	整数	8
decimal	固定小数点数	可変
numeric	固定小数点数	可変
real	浮動小数点数	4
double precision	浮動小数点数	8

decimal型とnumeric型とは同じもの
- いずれもSQL標準の型
【補】decimalは物理的にはゼロ埋めされずに保存される
- この点、char(n)よりはvarchar(n)に似ている
decimalの小数部があふれると丸めが行われる
丸めの結果、整数部の桁数があふれるとエラー

postgres=# CREATE TABLE tbl (col numeric(3,2));
CREATE TABLE

postgres=# INSERT INTO tbl (col) VALUES (1.23);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (1.234);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (9.99);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (9.995);
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.

整数型も同様

postgres=# DROP TABLE tbl;
DROP TABLE

postgres=# CREATE TABLE tbl (col smallint);
CREATE TABLE

postgres=# INSERT INTO tbl (col) VALUES (32767.4);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (32767.5);
ERROR:  smallint out of range

文字列

公式

型名	長さ	パディング
character varying(n), varchar(n)	最大n	なし
character(n), char(n)	固定n	あり
text	無制限	なし

バイナリ型

公式
標準SQLでは「BLOB」
PostgreSQLでは伝統的に「bytea」
可変長バイナリ列
- 画像、音声、各種ファイルなど

日付/時刻

公式

型名	サイズ/B	日付	時刻	TZ	分解能
timestamp [without time zone] [(p)]	8	o	o	x	1 ms
timestamp with time zone [(p)]	8	o	o	o	1 ms
date	4	o	x	x	1 day
time [without time zone] [(p)]	8	x	o	x	1 ms
time with time zone [(p)]	12	x	o	o	1 ms
interval [fields] [(p)]	16	-	-	-	1 ms

TZ: +09とかそういうの
fields
- '1 day'とか
- '0.5 hour'とか
【補】(p): 秒の小数点以下分解能 (0-6)

論理値

数値の1,0は使用不可能

postgres=# CREATE TABLE tbool (col boolean);
CREATE TABLE


postgres=# INSERT INTO tbool (col) VALUES (1);
ERROR:  column "col" is of type boolean but expression is of type integer
LINE 1: INSERT INTO tbool (col) VALUES (1);
                                        ^
HINT:  You will need to rewrite or cast the expression.

文字の'1'はOK

postgres=#  INSERT INTO tbool (col) VALUES ('1');
INSERT 0 1

'2'はダメ

postgres=# INSERT INTO tbool (col) VALUES ('2');
ERROR:  invalid input syntax for type boolean: "2"
LINE 1: INSERT INTO tbool (col) VALUES ('2');

true	false
't'	'f'
'true'	'false'
'y'	'n'
'yes'	'no'
'on'	'off'
'1'	'0'
TRUE	FALSE

大文字小文字の区別はない

連番

公式

型名	サイズ/バイト
smallserial	2
serial	4
bigserial	8

連番型に対応するシーケンスが自動生成される

postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | alphabets | table | postgres
 public | numbers   | table | postgres
(2 rows)


postgres=# CREATE TABLE tbl(id bigserial);
CREATE TABLE


postgres=# \d
             List of relations
 Schema |    Name    |   Type   |  Owner   
--------+------------+----------+----------
 public | alphabets  | table    | postgres
 public | numbers    | table    | postgres
 public | tbl        | table    | postgres
 public | tbl_id_seq | sequence | postgres
(4 rows)

tblのほか、tbl_id_seqが自動生成されている

OID

公式
PostgreSQL固有のデータ型
種々のデータベースオブジェクトのID
- データベースクラスタで一意
- システムテーブルの主キーとして使用されている

postgres=# SELECT oid, relname FROM pg_class ORDER BY oid ASC LIMIT 3;
 oid |              relname              
-----+-----------------------------------
 112 | pg_foreign_data_wrapper_oid_index
 113 | pg_foreign_server_oid_index
 174 | pg_user_mapping_oid_index
(3 rows)

キャストを用いて、テーブル名やインデックス名をOIDに変換できる

postgres=# SELECT 'pg_foreign_data_wrapper_oid_index';
             ?column?              
-----------------------------------
 pg_foreign_data_wrapper_oid_index
(1 row)


postgres=# SELECT 'pg_foreign_data_wrapper_oid_index'::regclass;
             regclass              
-----------------------------------
 pg_foreign_data_wrapper_oid_index
(1 row)


postgres=# SELECT 'pg_foreign_data_wrapper_oid_index'::regclass::int;
 int4 
------
  112
(1 row)


postgres=# SELECT oid, relname FROM pg_class WHERE oid = 'pg_foreign_data_wrapper_oid_index'::regclass;
 oid |              relname              
-----+-----------------------------------
 112 | pg_foreign_data_wrapper_oid_index
(1 row)

配列

1つのタプルに配列を放り込める
- 挿入
- 検索
- 更新
テーブル定義

postgres=# CREATE TABLE array_tbl(col int[]);
CREATE TABLE


postgres=# \d array_tbl 
              Table "public.array_tbl"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 col    | integer[] |           |          |

挿入

postgres=# insert into array_tbl (col) values ('{1,2,3}');
INSERT 0 1

postgres=# insert into array_tbl (col) values (Array[4,5,6]);
INSERT 0 1

検索

postgres=# SELECT * FROM array_tbl;
   col   
---------
 {1,2,3}
 {4,5,6}
(2 rows)


postgres=#  SELECT * FROM array_tbl WHERE (col[1] % 2) <> 0;
   col   
---------
 {1,2,3}
(1 row)


postgres=#  SELECT * FROM array_tbl WHERE 4 = ANY(col);
   col   
---------
 {4,5,6}
(1 row)

更新

postgres=# UPDATE array_tbl SET col[2] = 999;
UPDATE 2

postgres=# SELECT * FROM array_tbl;
    col    
-----------
 {1,999,3}
 {4,999,6}
(2 rows)

NULL

NULLは通常の演算子で比較などができない

【補】「値がないことのマーカー」
- unknown (Applicable, A-Mark)
- N/A (Inapplicable, I-Mark)
なので、値用の演算子は使えない
- 軒並みNULLを返す
IS NULL, IS NOT NULL使え

NULLを含む列のソートはNULLS FIRST / NULLS LASTを活用する

公式
【補】ORDER BYのデフォルトの振る舞い
- ASC: NULLS LAST
- DESC: NULLS FIRST
- 要するに、NULLは非NULLよりも大きな値であるかのように並ぶ

キャスト

postgres=# SELECT CAST('123' AS int), '123'::int;
 int4 | int4 
------+------
  123 |  123
(1 row)

postgres=# SELECT 123::text || '-' || 456::text;
 ?column? 
----------
 123-456
(1 row)

勉強日記

チラ裏

OSS-DB試験対策緑本 ch8 SQLとオブジェクト (1/2)

データの参照

文字の扱いについて

キーワードの大文字と小文字は区別しない

文字や日付情報はシングルクォートで囲む

列(カラム)、テーブル、検索条件の指定

ORDER BY

LIMITとOFFSET

DISTINCT

GROUP BYとHAVING

副問合せ

INとNOT IN

ANY

BETWEEN

結合

INNER JOIN

CROSS JOIN

OUTER JOIN

EXISTSとNOT EXISTS

UNION / EXCEPT / INTERSECT

INSERT / UPDATE / DELETE

INSERT

UPDATE

DELETE

データ型

数値

文字列

バイナリ型

日付/時刻

論理値

連番

OID

配列

NULL

NULLは通常の演算子で比較などができない

NULLを含む列のソートはNULLS FIRST / NULLS LASTを活用する

キャスト