1 -- regression test for the uuid datatype
2 -- creating test tables
6 text_field TEXT DEFAULT(now())
11 text_field TEXT DEFAULT(now())
13 -- inserting invalid data tests
15 INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F');
16 ERROR: invalid input syntax for uuid: "11111111-1111-1111-1111-111111111111F"
18 INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-1111-11111111111}');
19 ERROR: invalid input syntax for uuid: "{11111111-1111-1111-1111-11111111111}"
20 -- valid data but invalid format
21 INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-1111-111111111111');
22 ERROR: invalid input syntax for uuid: "111-11111-1111-1111-1111-111111111111"
23 INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222 ');
24 ERROR: invalid input syntax for uuid: "{22222222-2222-2222-2222-222222222222 "
26 INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G111-111111111111');
27 ERROR: invalid input syntax for uuid: "11111111-1111-1111-G111-111111111111"
28 INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-1111-111111111111');
29 ERROR: invalid input syntax for uuid: "11+11111-1111-1111-1111-111111111111"
30 --inserting three input formats
31 INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
32 INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
33 INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
34 -- retrieving the inserted data
35 SELECT guid_field FROM guid1;
37 --------------------------------------
38 11111111-1111-1111-1111-111111111111
39 22222222-2222-2222-2222-222222222222
40 3f3e3c3b-3a30-3938-3736-353433a2313e
44 SELECT guid_field FROM guid1 ORDER BY guid_field ASC;
46 --------------------------------------
47 11111111-1111-1111-1111-111111111111
48 22222222-2222-2222-2222-222222222222
49 3f3e3c3b-3a30-3938-3736-353433a2313e
52 SELECT guid_field FROM guid1 ORDER BY guid_field DESC;
54 --------------------------------------
55 3f3e3c3b-3a30-3938-3736-353433a2313e
56 22222222-2222-2222-2222-222222222222
57 11111111-1111-1111-1111-111111111111
61 SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e';
68 SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111';
75 SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222-2222-2222-222222222222';
82 SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222';
89 SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222222';
96 SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222';
102 -- btree and hash index creation test
103 CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
104 CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
106 CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
108 INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
109 ERROR: duplicate key value violates unique constraint "guid1_unique_btree"
110 -- check to see whether the new indexes are actually there
111 SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%';
117 -- populating the test tables with additional records
118 INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-4444-444444444444');
119 INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
120 INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}');
121 INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e');
123 SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid_field = g2.guid_field;
129 SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_field = g2.guid_field WHERE g2.guid_field IS NULL;
136 DROP TABLE guid1, guid2 CASCADE;