postgreSQL その25 SQL データ型その1

postgreSQL その24 SQL INSERT/UPDATE/DELETE文のつづき

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

[P.227]

■数値

数値型には下表の種類がある

数値型

型名	サイズ	範囲
smallint	2バイト	-32768　から +32767
integer、int	4バイト	-2147483648 から +2147483647
bigint	8バイト	-9223372036854775808 から +9223372036854775807
decimal	可変長	1000桁
numeric	可変長	1000桁
real	4バイト	6桁の精度
double	8バイト	15桁の精度

各データ型について、範囲外の数値を格納しようとするとエラーになる。

可変長のnumericやdecimalは、下記のように精度を指定できる。

testdb=# select * from tb1;
c1 | c2 | c3 | c4
----+-----+----+-----
2 | | | AAA
3 | CCC | | AAA
4 | DDD | | AAA
1 | | | ZZZ
1 | | | ZZZ
(5 rows)

・データ型の範囲
testdb=# update tb1 set c1=2147483647 where c1=1;
UPDATE 2
testdb=# select * from tb1;
c1 | c2 | c3 | c4
------------+-----+----+-----
2 | | | AAA
3 | CCC | | AAA
4 | DDD | | AAA
2147483647 | | | ZZZ　　　　　　　　　←int型の最大値は格納できる
2147483647 | | | ZZZ　　　　　　　　　←int型の最大値は格納できる
(5 rows)

testdb=# update tb1 set c1=2147483648 where c1=2;
ERROR: integer out of range　　　　　　　　←えら～
testdb=# select * from tb1;
c1 | c2 | c3 | c4
------------+-----+----+-----
2 | | | AAA　　　　　　　　　　　←int型の最大値を超える値は格納できない
3 | CCC | | AAA
4 | DDD | | AAA
2147483647 | | | ZZZ
2147483647 | | | ZZZ
(5 rows)

いったんtb1の値をupdate文で下記のように整える

testdb=# select * from tb1;
c1 | c2 | c3 | c4
----+-----+------------+-----
2 | | | AAA
3 | CCC | | AAA
4 | DDD | | AAA
1 | BBB | 2018-05-19 | BBB
5 | EEE | 2018-05-19 | EEE
(5 rows)

・numericやdecimalなどの可変長データ型

numericやdecimalなどは下記のように精度を指定できる。

c1に9.99を代入

testdb=# insert into tb2 values (9.99);
INSERT 0 1　　　　　　　　　　　　　　　　　　　　　　　←格納できた
testdb=# select * from tb2;
c1
------
9.99
(1 row)

c1に9.995を代入
testdb=# insert into tb2 values (9.995);
ERROR: numeric field overflow　　　　　　　　　　　←9.995は四捨五入され10になってしまうのでNG
DETAIL: A field with precision 3, scale 2 must round to an absolute value less than 10^1.
testdb=# select * from tb2;
c1
------
9.99
(1 row)

・整数値の指定の仕方

下記は全部OK

testdb=# update tb1 set c1=+10 where c1=1;
UPDATE 1
testdb=# update tb1 set c1='+20' where c1='2';
UPDATE 1
testdb=# update tb1 set c1=-30 where c1='3';
UPDATE 1
testdb=# update tb1 set c2= '-40' where c1= 4;
UPDATE 1
testdb=# update tb1 set c1 ='50' where c1 ='+5';
UPDATE 1
testdb=# update tb1 set c1= '-40' where c1= 4;
UPDATE 1
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)

■文字列

文字列を表現する型は、下表のように3種類ある。

文字列型

型名	説明
char(n)、character(n)	空白で埋められた固定長の文字列
varchar(n)、character varying(n)	文字数の上限値を指定した可変長の文字列
text	可変長の文字列

・char型

char型は固定長の文字列を格納する場合に使用する。nには文字数を指定する。

（バイト数ではない）。　nを省略した場合は、char(1)を指定したことになる。nに満たない文字列を格納した場合は、文字の右側が半角の空白文字で埋められる。

たとえば、char(5)で定義した列に「鈴木」を挿入した場合、列には「鈴木△△△」（△は半角スペース）が格納される。　一方、nより大きい文字数を格納しようとするとエラーになる。

ただし、例外的に文字のすべてが半角空白文字だった場合は、nまで切り詰められて格納される。

・varchar型

varchar型は可変長の文字列を格納する場合に使用する。charと同じくnには文字数を指定する。nを省略した場合、上限値無しとして扱われる。nより大きい文字を格納しようとした場合、および文字のすべてが半角の空白文字だった場合の例外処理はchar型と同じ。

・text型

text型は、varchar型と同じく可変長の文字列を格納する場合に使用する。

上限値はなく、varcharのnを省略した場合と同様のふるまいになる。　実際には、text型やnの指定を省略した varchar型は、1GBの文字格納領域が上限となる。

[準備]

・char型

tb3のc1はchar(4)なので、日本語4文字、ASCII4文字、日本語5文字、ASCII5文字を入れてみる

testdb=# insert into tb3(c1) values ('四字熟語'),('a1b2');
INSERT 0 2
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
(2 rows)

testdb=# insert into tb3(c1) values ('四文字熟語');
ERROR: value too long for type character(4)　　　　　　←えら～
testdb=# insert into tb3(c1) values ('a1b2c');
ERROR: value too long for type character(4)　　　　　　←バイト数に関係なく文字数制限でえら～
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
(2 rows)

・変な文字

testdb=# insert into tb3(c1) values ('a1b'');　　　←4文字目がシングルクォーテーション
testdb'# ;
testdb'# ^C　　　　　　　　　　←「Ctrl」+「c」
testdb=# ←もどった

testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
(2 rows)

→当然挿入されてない

testdb=# insert into tb3(c1) values ('a1b"');　　　←4文字目がダブルクォーテーション
INSERT 0 1
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
a1b" | | | |　　　　　　　　　←ダブルクオーテーションは文字として認識されたんですけど先生
(3 rows)

testdb=# insert into tb3(c1) values ('a1　b');　　←3文字目が全角スペース
INSERT 0 1
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
a1b" | | | |
a1　b | | | |　　　　　　　　　←普通に入った
(4 rows)

下記は全部OKだった

testdb=# insert into tb3(c1) values ('$#]&');
INSERT 0 1
testdb=# insert into tb3(c1) values (')(');
INSERT 0 1
testdb=# insert into tb3(c1) values ('.,^/');
INSERT 0 1
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | | |
a1b2 | | | |
a1b" | | | |
a1　b | | | |
$#]& | | | |
)( | | | |
.,^/ | | | |
(7 rows)

testdb=# select * from tb3 where c1='.,^/';
c1 | c2 | c3 | c4 | c5
------+----+----+----+----
.,^/ | | | |
(1 row)

→シングルクォーテーション以外はたいてい大丈夫くさい

testdb=# update tb3 set c3='麹' where c1='四字熟語';
UPDATE 1
testdb=# select * from tb3 where c1='四字熟語';
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | 麹 | |
(1 row)

testdb=# update tb3 set c3='麹町' where c1='四字熟語';
ERROR: value too long for type character(1)
testdb=# select * from tb3 where c1='四字熟語';
c1 | c2 | c3 | c4 | c5
----------+----+----+----+----
四字熟語 | | 麹 | |
(1 row)

・varchar型

testdb=# update tb3 set c2='おまんこ' where c1='四字熟語';
UPDATE 1
testdb=# update tb3 set c2='うんこっこ' where c1='a1b2';
ERROR: value too long for type character varying(4)
testdb=# select * from tb3;
c1 | c2 | c3 | c4 | c5
----------+----------+----+----+----
a1b2 | | | |　　　　　　←5文字ではえら～
a1b" | | | |
a1　b | | | |
$#]& | | | |
)( | | | |
.,^/ | | | |
四字熟語 | おまんこ | 麹 | |　　　　←4文字なら格納できた
(7 rows)

testdb=# update tb3 set c4='じゅげむじゅげむ五劫の擦り切れ海砂利水魚のうんぽこぴゅーーーーーーーーー！！！！' where c1='四字熟語';
UPDATE 1
testdb=# select * from tb3 where c1='四字熟語';
c1 | c2 | c3 | c4 | c5
----------+----------+----+------------------------------------------------------------------------+----
四字熟語 | おまんこ | 麹 | じゅげむじゅげむ五劫の擦り切れ海砂利水魚のうんぽこぴゅーーーーーーーーー！！！！ |
(1 row)

・text型

testdb=# update tb3 set c5='隣の竹垣はよく客を食う柿だーーーーーーーーーーー！！！！' where c1='a1b2';
UPDATE 1
testdb=# select * from tb3 where c1='a1b2';
c1 | c2 | c3 | c4 | c5
------+----+----+----+----------------------------------------------------------
a1b2 | | | | 隣の竹垣はよく客を食う柿だーーーーーーーーーーー！！！！
(1 row)

■日付/時刻

日付や時刻などの時間を表現する型は6種類ある。

日付/時刻型

型名	サイズ	説明	サンプル
timestamp [without time zone]	8バイト	日付と時刻（タイムゾーン無し）	2017-07-01 12:00:00.000000
timestamp with time zone	8バイト	日付と時刻（タイムゾーンあり）	2017-07-01 12:00:00.000000+09
date	4バイト	日付（時刻無し）	2017-07-01
time [without time zone]	8バイト	時刻（タイムゾーン無し）	12:00:00.000000
time with time zone	12バイト	時刻（タイムゾーンあり）	12:00:00.000000+09
interval [fields]	12バイト	時間間隔	'163 days 18:53:00.21035'

timeやtimestampで出力される時刻は、マイクロ秒まで表示される。

dateやtimestamp型への入力方法は多岐にわたる。たとえば、2017年7月1日という年月日をdate型として表す場合には、次のような書式を使用できる。

'2017-07-01'
'2017/07/01'
'2017-Jul-01'
'Jul 01 2017'

interval型は、時間間隔を表現するやや特殊なデータ型。

「interval '数値単位'」という形で記述される。　単位は、year、month、day、hour、minuiteなどが使用でき、それらの複数形も受け付ける。

[例]

testdb=# select now();
now
-------------------------------
2018-05-20 01:09:34.650206+09
(1 row)

testdb=# select current_date - interval '1 day';
?column?
---------------------
2018-05-19 00:00:00
(1 row)

testdb=# select current_date - interval '1 hour';
?column?
---------------------
2018-05-19 23:00:00
(1 row)

testdb=# select current_date - interval '0.1 hour';
?column?
---------------------
2018-05-19 23:54:00
(1 row)

■論理値

論理値を表す方は一種類のみ

論理値型

型名	サイズ	説明
boolean	1バイト	真か偽、もしくは不定（NULL）の状態を保持

真偽値の表現には次の文字が使用できる。（大文字と小文字の区別なし）

't' 、'f'
'true' 、'false'
'y' 、'n'
'yes' 、'no'
'on' 、'off'
'1'、'0'
TRUE、FALSE　（シングルクォートで囲まない）

※真でも偽でもない状態として、NULLを使用することもできる。

※数値の1と0は、boolean型には使えない

【検証】

testdb=# create table tb4 (c1 boolean);
CREATE TABLE
testdb=# insert into tb4 values ('1');
INSERT 0 1
testdb=# insert into tb4 values ('1'),('False'),('n'),(TRUE),(false),('yes');
INSERT 0 6
testdb=# select * from tb4;
c1
----
t
f
f
t
f
t
(6 rows)

testdb=# insert into tb4 values (0);
ERROR: column "c1" is of type boolean but expression is of type integer
LINE 1: insert into tb4 values (0);
^
HINT: You will need to rewrite or cast the expression.

testdb=# insert into tb4 values (NULL),('on');
INSERT 0 2
testdb=# select * from tb4;
c1
----
t
f
f
t
f
t
f

t
(9 rows)

postgreSQL その25 SQL データ型 その1

■数値

■文字列

・char型

・変な文字

・varchar型

・text型

■日付/時刻

■論理値

postgreSQL その25 SQL データ型その1