src/test/regress/sql/sequence.sql

   1 ---
   2 --- test creation of SERIAL column
   3 ---
   4
   5 CREATE TABLE serialTest (f1 text, f2 serial);
   6
   7 INSERT INTO serialTest VALUES ('foo');
   8 INSERT INTO serialTest VALUES ('bar');
   9 INSERT INTO serialTest VALUES ('force', 100);
  10 INSERT INTO serialTest VALUES ('wrong', NULL);
  11
  12 SELECT * FROM serialTest;
  13
  14 -- test smallserial / bigserial
  15 CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
  16   f5 bigserial, f6 serial8);
  17
  18 INSERT INTO serialTest2 (f1)
  19   VALUES ('test_defaults');
  20
  21 INSERT 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:
  28 INSERT INTO serialTest2 (f1, f3)
  29   VALUES ('bogus', -32769);
  30
  31 INSERT INTO serialTest2 (f1, f4)
  32   VALUES ('bogus', -32769);
  33
  34 INSERT INTO serialTest2 (f1, f3)
  35   VALUES ('bogus', 32768);
  36
  37 INSERT INTO serialTest2 (f1, f4)
  38   VALUES ('bogus', 32768);
  39
  40 INSERT INTO serialTest2 (f1, f5)
  41   VALUES ('bogus', -9223372036854775809);
  42
  43 INSERT INTO serialTest2 (f1, f6)
  44   VALUES ('bogus', -9223372036854775809);
  45
  46 INSERT INTO serialTest2 (f1, f5)
  47   VALUES ('bogus', 9223372036854775808);
  48
  49 INSERT INTO serialTest2 (f1, f6)
  50   VALUES ('bogus', 9223372036854775808);
  51
  52 SELECT * FROM serialTest2 ORDER BY f2 ASC;
  53
  54 SELECT nextval('serialTest2_f2_seq');
  55 SELECT nextval('serialTest2_f3_seq');
  56 SELECT nextval('serialTest2_f4_seq');
  57 SELECT nextval('serialTest2_f5_seq');
  58 SELECT nextval('serialTest2_f6_seq');
  59
  60 -- basic sequence operations using both text and oid references
  61 CREATE SEQUENCE sequence_test;
  62
  63 SELECT nextval('sequence_test'::text);
  64 SELECT nextval('sequence_test'::regclass);
  65 SELECT currval('sequence_test'::text);
  66 SELECT currval('sequence_test'::regclass);
  67 SELECT setval('sequence_test'::text, 32);
  68 SELECT nextval('sequence_test'::regclass);
  69 SELECT setval('sequence_test'::text, 99, false);
  70 SELECT nextval('sequence_test'::regclass);
  71 SELECT setval('sequence_test'::regclass, 32);
  72 SELECT nextval('sequence_test'::text);
  73 SELECT setval('sequence_test'::regclass, 99, false);
  74 SELECT nextval('sequence_test'::text);
  75
  76 DROP SEQUENCE sequence_test;
  77
  78 -- renaming sequences
  79 CREATE SEQUENCE foo_seq;
  80 ALTER TABLE foo_seq RENAME TO foo_seq_new;
  81 SELECT * FROM foo_seq_new;
  82 SELECT nextval('foo_seq_new');
  83 SELECT nextval('foo_seq_new');
  84 SELECT * FROM foo_seq_new;
  85 DROP SEQUENCE foo_seq_new;
  86
  87 -- renaming serial sequences
  88 ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
  89 INSERT INTO serialTest VALUES ('more');
  90 SELECT * FROM serialTest;
  91
  92 --
  93 -- Check dependencies of serial and ordinary sequences
  94 --
  95 CREATE TEMP SEQUENCE myseq2;
  96 CREATE TEMP SEQUENCE myseq3;
  97 CREATE TEMP TABLE t1 (
  98   f1 serial,
  99   f2 int DEFAULT nextval('myseq2'),
 100   f3 int DEFAULT nextval('myseq3'::text)
 101 );
 102 -- Both drops should fail, but with different error messages:
 103 DROP SEQUENCE t1_f1_seq;
 104 DROP SEQUENCE myseq2;
 105 -- This however will work:
 106 DROP SEQUENCE myseq3;
 107 DROP TABLE t1;
 108 -- Fails because no longer existent:
 109 DROP SEQUENCE t1_f1_seq;
 110 -- Now OK:
 111 DROP SEQUENCE myseq2;
 112
 113 --
 114 -- Alter sequence
 115 --
 116 CREATE SEQUENCE sequence_test2 START WITH 32;
 117
 118 SELECT nextval('sequence_test2');
 119
 120 ALTER SEQUENCE sequence_test2 RESTART WITH 24
 121          INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
 122 SELECT nextval('sequence_test2');
 123 SELECT nextval('sequence_test2');
 124 SELECT nextval('sequence_test2');
 125 SELECT nextval('sequence_test2');
 126 SELECT nextval('sequence_test2');
 127
 128 ALTER SEQUENCE sequence_test2 RESTART;
 129
 130 SELECT nextval('sequence_test2');
 131 SELECT nextval('sequence_test2');
 132 SELECT nextval('sequence_test2');
 133
 134 -- Information schema
 135 SELECT * FROM information_schema.sequences WHERE sequence_name IN
 136   ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
 137    'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
 138   ORDER BY sequence_name ASC;
 139
 140 -- Test comments
 141 COMMENT ON SEQUENCE asdf IS 'won''t work';
 142 COMMENT ON SEQUENCE sequence_test2 IS 'will work';
 143 COMMENT ON SEQUENCE sequence_test2 IS NULL;
 144
 145 -- Test lastval()
 146 CREATE SEQUENCE seq;
 147 SELECT nextval('seq');
 148 SELECT lastval();
 149 SELECT setval('seq', 99);
 150 SELECT lastval();
 151
 152 CREATE SEQUENCE seq2;
 153 SELECT nextval('seq2');
 154 SELECT lastval();
 155
 156 DROP SEQUENCE seq2;
 157 -- should fail
 158 SELECT lastval();
 159
 160 CREATE USER seq_user;
 161
 162 BEGIN;
 163 SET LOCAL SESSION AUTHORIZATION seq_user;
 164 CREATE SEQUENCE seq3;
 165 SELECT nextval('seq3');
 166 REVOKE ALL ON seq3 FROM seq_user;
 167 SELECT lastval();
 168 ROLLBACK;
 169
 170 -- Sequences should get wiped out as well:
 171 DROP TABLE serialTest, serialTest2;
 172
 173 -- Make sure sequences are gone:
 174 SELECT * FROM information_schema.sequences WHERE sequence_name IN
 175   ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
 176    'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
 177   ORDER BY sequence_name ASC;
 178
 179 DROP USER seq_user;
 180 DROP SEQUENCE seq;