postgreSQL その26 SQL データ型その2

postgreSQL その25 SQL データ型その1のつづき

postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見

[P.230]

■連番

連番を表現する型は、下表のように2種類

連番

型名	サイズ	範囲
serial	4バイト	-2147483648　から +2147483647 (NULLは不可)
bigserial	8バイト	-9223372036854775808 から +9223372036854775807 (NULLは不可)

型名

サイズ

範囲

serial

4バイト

-2147483648　から +2147483647

(NULLは不可)

bigserial

8バイト

-9223372036854775808 から +9223372036854775807

(NULLは不可)

serial型で定義された列を作成した際（つまり、テーブルの作成時やserial型の列の追加時）に、serial/bigserial型に対応するシーケンスが自動的に作成される。

serial/bigserial型に定義された列は、作成されたシーケンスから1から順番に自動的に正の整数を取得していく。

serial型として定義された列は、ユーザがデータ型の範囲を超えない値を任意に入力することもできる。ただし、自動で採番される値と重複する恐れがあり望ましくない。

[準備]

[例]

testdb=# insert into serial_test(name) values ('hoge');
INSERT 0 1

testdb=# insert into serial_test(name) values ('hoge2');
INSERT 0 1

testdb=# insert into serial_test(name) values ('hoge3');
INSERT 0 1
testdb=# select * from serial_test;
id | name
----+-------
1 | hoge
2 | hoge2
3 | hoge3
(3 rows)

・serial型のidカラムに「4」を手でinser

testdb=# insert into serial_test values (4,'hoge4');
INSERT 0 1

【分かったこと】

・連番は最後に自動インサートされた番号の次の番号が振られる

・連番に手動インサートした番号は自動インサートの採番では無視される

・連番に不連続な番号、既存の番号を手動インサートできる

・連番に負の整数をインサートできる

・連番に実数をインサートしたら四捨五入された整数値が挿入される

・連番に文字をインサートしたらエラーになる

・deleteで行を削除した場合、削除された行が自動採番、手動採番に関係なく、削除後の連番は最後に自動採番された番号の次の番号が振られる

■OID

OIDは、PostgreSQL独自位のデータ型で、主にオブジェクト（テーブルやインデックス、関数など）の識別に用いられるID番号。

システムテーブルやシステムビューでは、オブジェクトの名前ではなく、このOIDを使ってオブジェクトの情報が管理されている。

4バイトの数値で表現され、データベースクラスタ全体で一意の値を取る。

テーブルやインデックスに割り当てられているOIDは、pg_classなどのシステムテーブルに対し、次のようなSQLを発行することで確認できる。

OIDは隠し列であるため、SELECTの列目で指定しない限りは参照することができない。

・・・中略・・・

OIDは、システムテーブルやシステムビューから、特定のオブジェクトの情報を取得したい場合に用いられる。

前述のようにpg_classなどからOIDを得ることもできるが、オブジェクトの名称からOIDへ自動的に変換する便利なデータ型を使うことができる。

たとえば、テーブルやインデックスであれば、次のようにregclassというデータ型へのキャストを利用することで、テーブル名やインデックス名を自動的にOIDに変換してくれる。

testdb=# \x
Expanded display is on.
testdb=# select * from pg_class;
-[ RECORD 1 ]-------+------------------------------------------------------
relname | getdbsize
relnamespace | 16395
reltype | 16398
reloftype | 0
relowner | 10
relam | 0
relfilenode | 16396
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | f
relisshared | f
relpersistence | p
relkind | v
relnatts | 1
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | t
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relfrozenxid | 0
relminmxid | 0
relacl |
reloptions |
relpartbound |
-[ RECORD 2 ]-------+------------------------------------------------------
relname | city
relnamespace | 16395
reltype | 16408
reloftype | 0
relowner | 10
relam | 0
relfilenode | 16406
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
relhasindex | f
relisshared | f
relpersistence | p
relkind | r
relnatts | 4
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relfrozenxid | 565
relminmxid | 1
relacl | {postgres=arwdDxt/postgres,punisuke=arwdDxt/postgres}
reloptions |
relpartbound |

・・・後略・・・

・テーブル名を自動的にOIDに変換

testdb=# select oid,relname from pg_class where oid = 'pg_type'::regclass;
oid | relname
------+---------
1247 | pg_type
(1 row)

testdb=# select oid,relname from pg_class where oid = 'serial_test'::regclass;
oid | relname
-------+-------------
16519 | serial_test
(1 row)

・テーブル名からOIDの取得

testdb=# select oid,relname from pg_class where relname = 'serial_test';
oid | relname
-------+-------------
16519 | serial_test
(1 row)

■配列

PostgreSQLでは、配列型をサポートしている。　通常、列は単一の数値や日付データ、あるいは文字列などを格納しているが、配列はこれらの複数の値を格納することが可能。

配列型はすべてのデータ型に対して使用でき、次のように定義する。

[書式]

create table array_tbl (c1 text[], c2 int[], c3 timestamp[], ・・・ );

int[3][4] のように配列の次元数と要素数を指定できるが、実際には次元数、要素数ともに可変長となる。すなわち、int[3][4] のように要素3×4の2次元配列として宣言しても、実際には3次元以上の配列にすることもできるし、要素数を5や6に増やすこともできる。

・配列の挿入

[例]

・配列カラムをもったテーブルを作成

・要素3個をもった1次元配列を挿入
testdb=# insert into arry_table values ('{1,2,3}');
INSERT 0 1
testdb=# select * from arry_table;
c1
---------
{1,2,3}
(1 row)

・要素4個をもった2次元配列を挿入

testdb=# insert into arry_table values ('{{4,5,6,7},{8,9,10,11}}');

INSERT 0 1
testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
(2 rows)

・要素2個をもった2次元配列と、要素2個持った1次元配列を同時に挿入

testdb=# insert into arry_table values ('{{12},{13}}'),('{14,15}');
INSERT 0 2
testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
(4 rows)

・間違った配列挿入

testdb=# insert into arry_table values ('{{1,2,3},{4,5}}');　　←要素数がちぐはぐな2次元配列
ERROR: malformed array literal: "{{1,2,3},{4,5}}"
LINE 1: insert into arry_table values ('{{1,2,3},{4,5}}');
^
DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
testdb=# insert into arry_table values ('{{1,2,3},{4,5,0}}');
INSERT 0 1

testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
(5 rows)

・配列の検索

[例]

・配列カラムを持ったテーブルの作成

testdb=# insert into array2 values ('{"AAA","BBB","CCC"}');
INSERT 0 1
testdb=# insert into array2 values ('{"XXX","YYY","ZZZ"}');
INSERT 0 1
testdb=# select * from array2;
c1
---------------
{AAA,BBB,CCC}
{XXX,YYY,ZZZ}
(2 rows)

・配列の1番目から2番目の要素を取得

testdb=# select c1[1:2] from array2;
c1
-----------
{AAA,BBB}
{XXX,YYY}
(2 rows)

testdb=# insert into array2 values ('{"lll","mmm","nnn","ooo"}');
INSERT 0 1
testdb=# insert into array2 values ('{"PPP"}');
INSERT 0 1
testdb=# select c1[1:2] from array2;
c1
-----------
{AAA,BBB}
{XXX,YYY}
{lll,mmm}
{PPP}
(4 rows)

testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
(5 rows)

testdb=# select c1[1:2] from arry_table;
c1
-------------------------
{1,2}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
(5 rows)

testdb=# select c1[1:2][1:2] from arry_table;
c1
---------------
{}
{{4,5},{8,9}}
{{12},{13}}
{}
{{1,2},{4,5}}
(5 rows)

testdb=# select c1[1:2][3:4] from arry_table;
c1
-----------------
{}
{{6,7},{10,11}}
{}
{}
{{3},{0}}
(5 rows)

testdb=# select c1[1][3:4] from arry_table;
c1
---------
{}
{{6,7}}
{}
{}
{{3}}
(5 rows)
testdb=# select c1[2][3:4] from arry_table;
c1
-----------------
{}
{{6,7},{10,11}}
{}
{}
{{3},{0}}
(5 rows)

・配列の2番目の要素が「YYY」の行を選択

testdb=# select * from array2;
c1
-------------------
{AAA,BBB,CCC}
{XXX,YYY,ZZZ}
{lll,mmm,nnn,ooo}
{PPP}
{PPP}
(5 rows)
testdb=# select * from array2 where c1[2] = 'YYY';
c1
---------------
{XXX,YYY,ZZZ}
(1 row)

・配列の2次元目2番目の要素が「9」の行を選択

testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
(5 rows)

testdb=# select * from arry_table where c1[2][2] = 9;
c1
-------------------------
{{4,5,6,7},{8,9,10,11}}
(1 row)

・配列の1次元目4番目の要素が「7」の行を選択
testdb=# select * from arry_table where c1[1][4] = 7;
c1
-------------------------
{{4,5,6,7},{8,9,10,11}}
(1 row)

・配列の更新

・第一要素が「AAA」の行の第4要素に「DDD」を追加

testdb=# select * from array2;
c1
-------------------
{AAA,BBB,CCC}
{XXX,YYY,ZZZ}
{lll,mmm,nnn,ooo}
{PPP}
{PPP}
(5 rows)
testdb=# update array2 set c1[4] = 'DDD' where c1[1] = 'AAA';
UPDATE 1
testdb=# select * from array2;
c1
-------------------
{XXX,YYY,ZZZ}
{lll,mmm,nnn,ooo}
{PPP}
{PPP}
{AAA,BBB,CCC,DDD}
(5 rows)

・第一要素が「AAA」の行の第4要素を「EEE」に更新

testdb=# update array2 set c1[4] = 'EEE' where c1[1] = 'AAA';
UPDATE 1
testdb=# select * from array2;
c1
-------------------
{XXX,YYY,ZZZ}
{lll,mmm,nnn,ooo}
{PPP}
{PPP}
{AAA,BBB,CCC,EEE}
(5 rows)

testdb=# update array2 set c1[4] = 'FFF' where 'AAA' = any(c1);
UPDATE 1
testdb=# select * from array2;
c1
-------------------
{XXX,YYY,ZZZ}
{lll,mmm,nnn,ooo}
{PPP}
{PPP}
{AAA,BBB,CCC,FFF}
(5 rows)

・1次元目が1、2次元目が4の値が7の行の1次元目が2、2次元目が4の値を12に更新

testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{4,5,6,7},{8,9,10,11}}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
(5 rows)

testdb=# update arry_table set c1[2][4] = 12 where c1[1][4] = 7;
UPDATE 1
testdb=# select * from arry_table;
c1
-------------------------
{1,2,3}
{{12},{13}}
{14,15}
{{1,2,3},{4,5,0}}
{{4,5,6,7},{8,9,10,12}}
(5 rows)

■NULL

NULLは、不定を表すデータ。　他の数値や文字列などと異なり、特殊な扱いをする。

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

数値や文字列には等号「=」や不等号「<、>」などの演算子を使うことができるが、NULLには適用できない。NULLであるかどうかを確認する場合は、「列名=NULL」ではなく、

「列名 IS [NOT] NULL」を用いる。

次の例では、IS　NULLとIS NOT NULLを使用して検索する例

[例]

testdb=# select * from tb1;
c1 | c2 | c3 | c4
-----+-----+------------+-----
10 | BBB | 2018-05-19 | BBB
20 | | | AAA
-30 | CCC | | AAA
50 | EEE | 2018-05-19 | EEE
-40 | -40 | | AAA
(5 rows)

testdb=# select * from tb1 where c2 is null;
c1 | c2 | c3 | c4
----+----+----+-----
20 | | | AAA
(1 row)
testdb=# select * from tb1 where c3 is NULL;
c1 | c2 | c3 | c4
-----+-----+----+-----
20 | | | AAA
-30 | CCC | | AAA
-40 | -40 | | AAA
(3 rows)

testdb=# select * from tb1 where c3 is 'NULL'; ←NULLをシングルクォートで囲っちゃいけない
ERROR: syntax error at or near "'NULL'"
LINE 1: select * from tb1 where c3 is 'NULL';
testdb=# select * from tb1 where c3 is not NULL;
c1 | c2 | c3 | c4
----+-----+------------+-----
10 | BBB | 2018-05-19 | BBB
50 | EEE | 2018-05-19 | EEE
(2 rows)

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

NULLは不定であるため、NULLを含む列のソートを行う場合は、NULLをどう扱うかを明示的に指定すると安全。　NULL値は、すべての非NULL値より大きい値として扱われるため、通常の昇順ソートでは、非NULL値の後に出力される。

そのため、昇順でソートしたい場合に、NULLである列を先に出力させたい場合は、

「ORDER BY 列名 NULLS FIRST」を使う。逆に、NULLである列を後に出力させたい場合は、

「ORDER BY 列名 NULLS LAST」とする。

[例]

testdb=# select * from tb1;
c1 | c2 | c3 | c4
-----+-----+------------+-----
10 | BBB | 2018-05-19 | BBB
20 | | | AAA
-30 | CCC | | AAA
50 | EEE | 2018-05-19 | EEE
-40 | -40 | | AAA
(5 rows)

testdb=# select * from tb1 order by c3 nulls first;
c1 | c2 | c3 | c4
-----+-----+------------+-----
20 | | | AAA
-30 | CCC | | AAA
-40 | -40 | | AAA
10 | BBB | 2018-05-19 | BBB
50 | EEE | 2018-05-19 | EEE
(5 rows)

testdb=# select * from tb1 order by c3;
c1 | c2 | c3 | c4
-----+-----+------------+-----
10 | BBB | 2018-05-19 | BBB
50 | EEE | 2018-05-19 | EEE
20 | | | AAA
-30 | CCC | | AAA
-40 | -40 | | AAA
(5 rows)

testdb=# select * from tb1 order by c3 nulls last;
c1 | c2 | c3 | c4
-----+-----+------------+-----
10 | BBB | 2018-05-19 | BBB
50 | EEE | 2018-05-19 | EEE
20 | | | AAA
-30 | CCC | | AAA
-40 | -40 | | AAA
(5 rows)

■キャスト

キャストとは、あるデータ型を別のデータ型に変換する処理。　

文字列型の列から取得した値を数値型の値と比較する場合や、異なるデータ型の数値どうしの剰余算を行いたい場合に使用する。

キャストには下記の2つの方法がある

[書式1]

データ::変換後のデータ型

[書式2]

CAST (データ AS 変換後のデータ型)

[例]

・int型のカラムは整数演算できる

testdb=# select c1 + 1 from tb1 where c1 = -40;
?column?
----------
-39
(1 row)

・文字列型カラムは整数演算できない

testdb=# select c2 + 1 from tb1 where c1 = -40;
ERROR: operator does not exist: text + integer
LINE 1: select c2 + 1 from tb1 where c1 = -40;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

・キャストしてみる

postgreSQL その26 SQL データ型 その2

■連番

■OID

■配列

・配列の挿入

・配列の検索

・配列の更新

■NULL

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

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

■キャスト

postgreSQL その26 SQL データ型その2