summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/uuid.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/uuid.out')
-rw-r--r--src/test/regress/expected/uuid.out159
1 files changed, 159 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..090103d
--- /dev/null
+++ b/src/test/regress/expected/uuid.out
@@ -0,0 +1,159 @@
+-- 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...
+ ^
+--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;