blob: 2221e36d439690d57e8ab9de679ffcb46710026c (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
-- regression test for the uuid datatype
-- creating test tables
CREATE TABLE guid1
(
guid_field UUID,
text_field TEXT DEFAULT(now())
);
CREATE TABLE guid2
(
guid_field UUID,
text_field TEXT DEFAULT(now())
);
-- inserting invalid data tests
-- too long
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F');
ERROR: invalid input syntax for type uuid: "11111111-1111-1111-1111-111111111111F"
LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-111...
^
-- too short
INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-1111-11111111111}');
ERROR: invalid input syntax for type uuid: "{11111111-1111-1111-1111-11111111111}"
LINE 1: INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-11...
^
-- valid data but invalid format
INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-1111-111111111111');
ERROR: invalid input syntax for type uuid: "111-11111-1111-1111-1111-111111111111"
LINE 1: INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-11...
^
INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222 ');
ERROR: invalid input syntax for type uuid: "{22222222-2222-2222-2222-222222222222 "
LINE 1: INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-22...
^
-- invalid data
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G111-111111111111');
ERROR: invalid input syntax for type uuid: "11111111-1111-1111-G111-111111111111"
LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G11...
^
INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-1111-111111111111');
ERROR: invalid input syntax for type uuid: "11+11111-1111-1111-1111-111111111111"
LINE 1: INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-111...
^
--inserting three input formats
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
-- retrieving the inserted data
SELECT guid_field FROM guid1;
guid_field
--------------------------------------
11111111-1111-1111-1111-111111111111
22222222-2222-2222-2222-222222222222
3f3e3c3b-3a30-3938-3736-353433a2313e
(3 rows)
-- ordering test
SELECT guid_field FROM guid1 ORDER BY guid_field ASC;
guid_field
--------------------------------------
11111111-1111-1111-1111-111111111111
22222222-2222-2222-2222-222222222222
3f3e3c3b-3a30-3938-3736-353433a2313e
(3 rows)
SELECT guid_field FROM guid1 ORDER BY guid_field DESC;
guid_field
--------------------------------------
3f3e3c3b-3a30-3938-3736-353433a2313e
22222222-2222-2222-2222-222222222222
11111111-1111-1111-1111-111111111111
(3 rows)
-- = operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e';
count
-------
1
(1 row)
-- <> operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111';
count
-------
2
(1 row)
-- < operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222-2222-2222-222222222222';
count
-------
1
(1 row)
-- <= operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222';
count
-------
2
(1 row)
-- > operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222222';
count
-------
1
(1 row)
-- >= operator test
SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222';
count
-------
2
(1 row)
-- btree and hash index creation test
CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
-- populating the test tables with additional records
INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-4444-444444444444');
INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_field = g2.guid_field WHERE g2.guid_field IS NULL;
count
-------
1
(1 row)
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
|
