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-- inserting invalid data tests 14-- too long 15INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F'); 16ERROR: invalid input syntax for type uuid: "11111111-1111-1111-1111-111111111111F" 17LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-111... 18 ^ 19-- too short 20INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-1111-11111111111}'); 21ERROR: invalid input syntax for type uuid: "{11111111-1111-1111-1111-11111111111}" 22LINE 1: INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-11... 23 ^ 24-- valid data but invalid format 25INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-1111-111111111111'); 26ERROR: invalid input syntax for type uuid: "111-11111-1111-1111-1111-111111111111" 27LINE 1: INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-11... 28 ^ 29INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222 '); 30ERROR: invalid input syntax for type uuid: "{22222222-2222-2222-2222-222222222222 " 31LINE 1: INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-22... 32 ^ 33-- invalid data 34INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G111-111111111111'); 35ERROR: invalid input syntax for type uuid: "11111111-1111-1111-G111-111111111111" 36LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G11... 37 ^ 38INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-1111-111111111111'); 39ERROR: invalid input syntax for type uuid: "11+11111-1111-1111-1111-111111111111" 40LINE 1: INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-111... 41 ^ 42--inserting three input formats 43INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); 44INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}'); 45INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e'); 46-- retrieving the inserted data 47SELECT guid_field FROM guid1; 48 guid_field 49-------------------------------------- 50 11111111-1111-1111-1111-111111111111 51 22222222-2222-2222-2222-222222222222 52 3f3e3c3b-3a30-3938-3736-353433a2313e 53(3 rows) 54 55-- ordering test 56SELECT guid_field FROM guid1 ORDER BY guid_field ASC; 57 guid_field 58-------------------------------------- 59 11111111-1111-1111-1111-111111111111 60 22222222-2222-2222-2222-222222222222 61 3f3e3c3b-3a30-3938-3736-353433a2313e 62(3 rows) 63 64SELECT guid_field FROM guid1 ORDER BY guid_field DESC; 65 guid_field 66-------------------------------------- 67 3f3e3c3b-3a30-3938-3736-353433a2313e 68 22222222-2222-2222-2222-222222222222 69 11111111-1111-1111-1111-111111111111 70(3 rows) 71 72-- = operator test 73SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e'; 74 count 75------- 76 1 77(1 row) 78 79-- <> operator test 80SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111'; 81 count 82------- 83 2 84(1 row) 85 86-- < operator test 87SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222-2222-2222-222222222222'; 88 count 89------- 90 1 91(1 row) 92 93-- <= operator test 94SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222'; 95 count 96------- 97 2 98(1 row) 99 100-- > operator test 101SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222222'; 102 count 103------- 104 1 105(1 row) 106 107-- >= operator test 108SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222'; 109 count 110------- 111 2 112(1 row) 113 114-- btree and hash index creation test 115CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field); 116CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field); 117-- unique index test 118CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field); 119-- should fail 120INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); 121ERROR: duplicate key value violates unique constraint "guid1_unique_btree" 122DETAIL: Key (guid_field)=(11111111-1111-1111-1111-111111111111) already exists. 123-- check to see whether the new indexes are actually there 124SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%'; 125 count 126------- 127 3 128(1 row) 129 130-- populating the test tables with additional records 131INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-4444-444444444444'); 132INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); 133INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}'); 134INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e'); 135-- join test 136SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid_field = g2.guid_field; 137 count 138------- 139 3 140(1 row) 141 142SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_field = g2.guid_field WHERE g2.guid_field IS NULL; 143 count 144------- 145 1 146(1 row) 147 148-- clean up 149DROP TABLE guid1, guid2 CASCADE; 150