diff options
Diffstat (limited to 'src/test/regress/expected/uuid.out')
| -rw-r--r-- | src/test/regress/expected/uuid.out | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out new file mode 100644 index 0000000..8e7f219 --- /dev/null +++ b/src/test/regress/expected/uuid.out @@ -0,0 +1,172 @@ +-- 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... + ^ +-- test non-error-throwing API +SELECT pg_input_is_valid('11', 'uuid'); + pg_input_is_valid +------------------- + f +(1 row) + +SELECT * FROM pg_input_error_info('11', 'uuid'); + message | detail | hint | sql_error_code +------------------------------------------+--------+------+---------------- + invalid input syntax for type uuid: "11" | | | 22P02 +(1 row) + +--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); +-- unique index test +CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field); +-- should fail +INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); +ERROR: duplicate key value violates unique constraint "guid1_unique_btree" +DETAIL: Key (guid_field)=(11111111-1111-1111-1111-111111111111) already exists. +-- check to see whether the new indexes are actually there +SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%'; + count +------- + 3 +(1 row) + +-- 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'); +-- join test +SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid_field = g2.guid_field; + count +------- + 3 +(1 row) + +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) + +-- generation test +TRUNCATE guid1; +INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid()); +INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid()); +SELECT count(DISTINCT guid_field) FROM guid1; + count +------- + 2 +(1 row) + +-- clean up +DROP TABLE guid1, guid2 CASCADE; |