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