1-- regression test for the uuid datatype
2-- creating test tables
3CREATE TABLE guid1
4(
5	guid_field UUID,
6	text_field TEXT DEFAULT(now())
7);
8CREATE TABLE guid2
9(
10	guid_field UUID,
11	text_field TEXT DEFAULT(now())
12);
13
14-- inserting invalid data tests
15-- too long
16INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F');
17-- too short
18INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-1111-11111111111}');
19-- valid data but invalid format
20INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-1111-111111111111');
21INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222 ');
22-- invalid data
23INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G111-111111111111');
24INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-1111-111111111111');
25
26--inserting three input formats
27INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
28INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
29INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
30
31-- retrieving the inserted data
32SELECT guid_field FROM guid1;
33
34-- ordering test
35SELECT guid_field FROM guid1 ORDER BY guid_field ASC;
36SELECT guid_field FROM guid1 ORDER BY guid_field DESC;
37
38-- = operator test
39SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e';
40
41-- <> operator test
42SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111';
43
44-- < operator test
45SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222-2222-2222-222222222222';
46
47-- <= operator test
48SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222';
49
50-- > operator test
51SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222222';
52
53-- >= operator test
54SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222';
55
56-- btree and hash index creation test
57CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
58CREATE INDEX guid1_hash  ON guid1 USING HASH  (guid_field);
59
60-- unique index test
61CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
62-- should fail
63INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
64
65-- check to see whether the new indexes are actually there
66SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%';
67
68-- populating the test tables with additional records
69INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-4444-444444444444');
70INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
71INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
72INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
73
74-- join test
75SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid_field = g2.guid_field;
76SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_field = g2.guid_field WHERE g2.guid_field IS NULL;
77
78-- clean up
79DROP TABLE guid1, guid2 CASCADE;
80