1--- 2--- test creation of SERIAL column 3--- 4 5CREATE TABLE serialTest (f1 text, f2 serial); 6 7INSERT INTO serialTest VALUES ('foo'); 8INSERT INTO serialTest VALUES ('bar'); 9INSERT INTO serialTest VALUES ('force', 100); 10INSERT INTO serialTest VALUES ('wrong', NULL); 11 12SELECT * FROM serialTest; 13 14-- test smallserial / bigserial 15CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, 16 f5 bigserial, f6 serial8); 17 18INSERT INTO serialTest2 (f1) 19 VALUES ('test_defaults'); 20 21INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) 22 VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807, 23 9223372036854775807), 24 ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808, 25 -9223372036854775808); 26 27-- All these INSERTs should fail: 28INSERT INTO serialTest2 (f1, f3) 29 VALUES ('bogus', -32769); 30 31INSERT INTO serialTest2 (f1, f4) 32 VALUES ('bogus', -32769); 33 34INSERT INTO serialTest2 (f1, f3) 35 VALUES ('bogus', 32768); 36 37INSERT INTO serialTest2 (f1, f4) 38 VALUES ('bogus', 32768); 39 40INSERT INTO serialTest2 (f1, f5) 41 VALUES ('bogus', -9223372036854775809); 42 43INSERT INTO serialTest2 (f1, f6) 44 VALUES ('bogus', -9223372036854775809); 45 46INSERT INTO serialTest2 (f1, f5) 47 VALUES ('bogus', 9223372036854775808); 48 49INSERT INTO serialTest2 (f1, f6) 50 VALUES ('bogus', 9223372036854775808); 51 52SELECT * FROM serialTest2 ORDER BY f2 ASC; 53 54SELECT nextval('serialTest2_f2_seq'); 55SELECT nextval('serialTest2_f3_seq'); 56SELECT nextval('serialTest2_f4_seq'); 57SELECT nextval('serialTest2_f5_seq'); 58SELECT nextval('serialTest2_f6_seq'); 59 60-- basic sequence operations using both text and oid references 61CREATE SEQUENCE sequence_test; 62CREATE SEQUENCE IF NOT EXISTS sequence_test; 63 64SELECT nextval('sequence_test'::text); 65SELECT nextval('sequence_test'::regclass); 66SELECT currval('sequence_test'::text); 67SELECT currval('sequence_test'::regclass); 68SELECT setval('sequence_test'::text, 32); 69SELECT nextval('sequence_test'::regclass); 70SELECT setval('sequence_test'::text, 99, false); 71SELECT nextval('sequence_test'::regclass); 72SELECT setval('sequence_test'::regclass, 32); 73SELECT nextval('sequence_test'::text); 74SELECT setval('sequence_test'::regclass, 99, false); 75SELECT nextval('sequence_test'::text); 76DISCARD SEQUENCES; 77SELECT currval('sequence_test'::regclass); 78 79DROP SEQUENCE sequence_test; 80 81-- renaming sequences 82CREATE SEQUENCE foo_seq; 83ALTER TABLE foo_seq RENAME TO foo_seq_new; 84SELECT * FROM foo_seq_new; 85SELECT nextval('foo_seq_new'); 86SELECT nextval('foo_seq_new'); 87SELECT * FROM foo_seq_new; 88DROP SEQUENCE foo_seq_new; 89 90-- renaming serial sequences 91ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo; 92INSERT INTO serialTest VALUES ('more'); 93SELECT * FROM serialTest; 94 95-- 96-- Check dependencies of serial and ordinary sequences 97-- 98CREATE TEMP SEQUENCE myseq2; 99CREATE TEMP SEQUENCE myseq3; 100CREATE TEMP TABLE t1 ( 101 f1 serial, 102 f2 int DEFAULT nextval('myseq2'), 103 f3 int DEFAULT nextval('myseq3'::text) 104); 105-- Both drops should fail, but with different error messages: 106DROP SEQUENCE t1_f1_seq; 107DROP SEQUENCE myseq2; 108-- This however will work: 109DROP SEQUENCE myseq3; 110DROP TABLE t1; 111-- Fails because no longer existent: 112DROP SEQUENCE t1_f1_seq; 113-- Now OK: 114DROP SEQUENCE myseq2; 115 116-- 117-- Alter sequence 118-- 119 120ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24 121 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; 122 123CREATE SEQUENCE sequence_test2 START WITH 32; 124 125SELECT nextval('sequence_test2'); 126 127ALTER SEQUENCE sequence_test2 RESTART WITH 24 128 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; 129SELECT nextval('sequence_test2'); 130SELECT nextval('sequence_test2'); 131SELECT nextval('sequence_test2'); 132SELECT nextval('sequence_test2'); 133SELECT nextval('sequence_test2'); 134 135ALTER SEQUENCE sequence_test2 RESTART; 136 137SELECT nextval('sequence_test2'); 138SELECT nextval('sequence_test2'); 139SELECT nextval('sequence_test2'); 140 141-- Information schema 142SELECT * FROM information_schema.sequences WHERE sequence_name IN 143 ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq', 144 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq') 145 ORDER BY sequence_name ASC; 146 147-- Test comments 148COMMENT ON SEQUENCE asdf IS 'won''t work'; 149COMMENT ON SEQUENCE sequence_test2 IS 'will work'; 150COMMENT ON SEQUENCE sequence_test2 IS NULL; 151 152-- Test lastval() 153CREATE SEQUENCE seq; 154SELECT nextval('seq'); 155SELECT lastval(); 156SELECT setval('seq', 99); 157SELECT lastval(); 158DISCARD SEQUENCES; 159SELECT lastval(); 160 161CREATE SEQUENCE seq2; 162SELECT nextval('seq2'); 163SELECT lastval(); 164 165DROP SEQUENCE seq2; 166-- should fail 167SELECT lastval(); 168 169CREATE USER regress_seq_user; 170 171-- privileges tests 172 173-- nextval 174BEGIN; 175SET LOCAL SESSION AUTHORIZATION regress_seq_user; 176CREATE SEQUENCE seq3; 177REVOKE ALL ON seq3 FROM regress_seq_user; 178GRANT SELECT ON seq3 TO regress_seq_user; 179SELECT nextval('seq3'); 180ROLLBACK; 181 182BEGIN; 183SET LOCAL SESSION AUTHORIZATION regress_seq_user; 184CREATE SEQUENCE seq3; 185REVOKE ALL ON seq3 FROM regress_seq_user; 186GRANT UPDATE ON seq3 TO regress_seq_user; 187SELECT nextval('seq3'); 188ROLLBACK; 189 190BEGIN; 191SET LOCAL SESSION AUTHORIZATION regress_seq_user; 192CREATE SEQUENCE seq3; 193REVOKE ALL ON seq3 FROM regress_seq_user; 194GRANT USAGE ON seq3 TO regress_seq_user; 195SELECT nextval('seq3'); 196ROLLBACK; 197 198-- currval 199BEGIN; 200SET LOCAL SESSION AUTHORIZATION regress_seq_user; 201CREATE SEQUENCE seq3; 202SELECT nextval('seq3'); 203REVOKE ALL ON seq3 FROM regress_seq_user; 204GRANT SELECT ON seq3 TO regress_seq_user; 205SELECT currval('seq3'); 206ROLLBACK; 207 208BEGIN; 209SET LOCAL SESSION AUTHORIZATION regress_seq_user; 210CREATE SEQUENCE seq3; 211SELECT nextval('seq3'); 212REVOKE ALL ON seq3 FROM regress_seq_user; 213GRANT UPDATE ON seq3 TO regress_seq_user; 214SELECT currval('seq3'); 215ROLLBACK; 216 217BEGIN; 218SET LOCAL SESSION AUTHORIZATION regress_seq_user; 219CREATE SEQUENCE seq3; 220SELECT nextval('seq3'); 221REVOKE ALL ON seq3 FROM regress_seq_user; 222GRANT USAGE ON seq3 TO regress_seq_user; 223SELECT currval('seq3'); 224ROLLBACK; 225 226-- lastval 227BEGIN; 228SET LOCAL SESSION AUTHORIZATION regress_seq_user; 229CREATE SEQUENCE seq3; 230SELECT nextval('seq3'); 231REVOKE ALL ON seq3 FROM regress_seq_user; 232GRANT SELECT ON seq3 TO regress_seq_user; 233SELECT lastval(); 234ROLLBACK; 235 236BEGIN; 237SET LOCAL SESSION AUTHORIZATION regress_seq_user; 238CREATE SEQUENCE seq3; 239SELECT nextval('seq3'); 240REVOKE ALL ON seq3 FROM regress_seq_user; 241GRANT UPDATE ON seq3 TO regress_seq_user; 242SELECT lastval(); 243ROLLBACK; 244 245BEGIN; 246SET LOCAL SESSION AUTHORIZATION regress_seq_user; 247CREATE SEQUENCE seq3; 248SELECT nextval('seq3'); 249REVOKE ALL ON seq3 FROM regress_seq_user; 250GRANT USAGE ON seq3 TO regress_seq_user; 251SELECT lastval(); 252ROLLBACK; 253 254-- Sequences should get wiped out as well: 255DROP TABLE serialTest, serialTest2; 256 257-- Make sure sequences are gone: 258SELECT * FROM information_schema.sequences WHERE sequence_name IN 259 ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq', 260 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq') 261 ORDER BY sequence_name ASC; 262 263DROP USER regress_seq_user; 264DROP SEQUENCE seq; 265