データの参照
SELECT
文
文字の扱いについて
キーワードの大文字と小文字は区別しない
-
SELECT
とか- いっぱいある
文字や日付情報はシングルクォートで囲む
-
COPY
文はシングルクォートが必要- cf.
\copy
メタコマンドは不要
- cf.
列(カラム)、テーブル、検索条件の指定
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
だと両テーブルのカラムが出てくる
- cf.
-
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
の一般形
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)