Add some regression tests for serial pseudotypes.

[pg-rex/syncrep.git] / src / test / regress / sql / sequence.sql

diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 29ea691..97ffb60 100644 (file)
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -11,6 +11,52 @@ INSERT INTO serialTest VALUES ('wrong', NULL);
 
 SELECT * FROM serialTest;
 
+-- test smallserial / bigserial
+CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
+  f5 bigserial, f6 serial8);
+
+INSERT INTO serialTest2 (f1)
+  VALUES ('test_defaults');
+
+INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
+  VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807,
+          9223372036854775807),
+         ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808,
+          -9223372036854775808);
+
+-- All these INSERTs should fail:
+INSERT INTO serialTest2 (f1, f3)
+  VALUES ('bogus', -32769);
+
+INSERT INTO serialTest2 (f1, f4)
+  VALUES ('bogus', -32769);
+
+INSERT INTO serialTest2 (f1, f3)
+  VALUES ('bogus', 32768);
+
+INSERT INTO serialTest2 (f1, f4)
+  VALUES ('bogus', 32768);
+
+INSERT INTO serialTest2 (f1, f5)
+  VALUES ('bogus', -9223372036854775809);
+
+INSERT INTO serialTest2 (f1, f6)
+  VALUES ('bogus', -9223372036854775809);
+
+INSERT INTO serialTest2 (f1, f5)
+  VALUES ('bogus', 9223372036854775808);
+
+INSERT INTO serialTest2 (f1, f6)
+  VALUES ('bogus', 9223372036854775808);
+
+SELECT * FROM serialTest2 ORDER BY f2 ASC;
+
+SELECT nextval('serialTest2_f2_seq');
+SELECT nextval('serialTest2_f3_seq');
+SELECT nextval('serialTest2_f4_seq');
+SELECT nextval('serialTest2_f5_seq');
+SELECT nextval('serialTest2_f6_seq');
+
 -- basic sequence operations using both text and oid references
 CREATE SEQUENCE sequence_test;
 
@@ -86,7 +132,10 @@ SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 
 -- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN ('sequence_test2');
+SELECT * FROM information_schema.sequences WHERE sequence_name IN
+  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+  ORDER BY sequence_name ASC;
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
@@ -118,5 +167,14 @@ REVOKE ALL ON seq3 FROM seq_user;
 SELECT lastval();
 ROLLBACK;
 
+-- Sequences should get wiped out as well:
+DROP TABLE serialTest, serialTest2;
+
+-- Make sure sequences are gone:
+SELECT * FROM information_schema.sequences WHERE sequence_name IN
+  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+  ORDER BY sequence_name ASC;
+
 DROP USER seq_user;
 DROP SEQUENCE seq;