2 --- test creation of SERIAL column
4 CREATE TABLE serialTest (f1 text, f2 serial);
5 INSERT INTO serialTest VALUES ('foo');
6 INSERT INTO serialTest VALUES ('bar');
7 INSERT INTO serialTest VALUES ('force', 100);
8 INSERT INTO serialTest VALUES ('wrong', NULL);
9 ERROR: null value in column "f2" violates not-null constraint
10 DETAIL: Failing row contains (wrong, null).
11 SELECT * FROM serialTest;
19 -- test smallserial / bigserial
20 CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
21 f5 bigserial, f6 serial8);
22 INSERT INTO serialTest2 (f1)
23 VALUES ('test_defaults');
24 INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
25 VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807,
27 ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808,
28 -9223372036854775808);
29 -- All these INSERTs should fail:
30 INSERT INTO serialTest2 (f1, f3)
31 VALUES ('bogus', -32769);
32 ERROR: smallint out of range
33 INSERT INTO serialTest2 (f1, f4)
34 VALUES ('bogus', -32769);
35 ERROR: smallint out of range
36 INSERT INTO serialTest2 (f1, f3)
37 VALUES ('bogus', 32768);
38 ERROR: smallint out of range
39 INSERT INTO serialTest2 (f1, f4)
40 VALUES ('bogus', 32768);
41 ERROR: smallint out of range
42 INSERT INTO serialTest2 (f1, f5)
43 VALUES ('bogus', -9223372036854775809);
44 ERROR: bigint out of range
45 INSERT INTO serialTest2 (f1, f6)
46 VALUES ('bogus', -9223372036854775809);
47 ERROR: bigint out of range
48 INSERT INTO serialTest2 (f1, f5)
49 VALUES ('bogus', 9223372036854775808);
50 ERROR: bigint out of range
51 INSERT INTO serialTest2 (f1, f6)
52 VALUES ('bogus', 9223372036854775808);
53 ERROR: bigint out of range
54 SELECT * FROM serialTest2 ORDER BY f2 ASC;
55 f1 | f2 | f3 | f4 | f5 | f6
56 ---------------+-------------+--------+--------+----------------------+----------------------
57 test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808
58 test_defaults | 1 | 1 | 1 | 1 | 1
59 test_max_vals | 2147483647 | 32767 | 32767 | 9223372036854775807 | 9223372036854775807
62 SELECT nextval('serialTest2_f2_seq');
68 SELECT nextval('serialTest2_f3_seq');
74 SELECT nextval('serialTest2_f4_seq');
80 SELECT nextval('serialTest2_f5_seq');
86 SELECT nextval('serialTest2_f6_seq');
92 -- basic sequence operations using both text and oid references
93 CREATE SEQUENCE sequence_test;
94 SELECT nextval('sequence_test'::text);
100 SELECT nextval('sequence_test'::regclass);
106 SELECT currval('sequence_test'::text);
112 SELECT currval('sequence_test'::regclass);
118 SELECT setval('sequence_test'::text, 32);
124 SELECT nextval('sequence_test'::regclass);
130 SELECT setval('sequence_test'::text, 99, false);
136 SELECT nextval('sequence_test'::regclass);
142 SELECT setval('sequence_test'::regclass, 32);
148 SELECT nextval('sequence_test'::text);
154 SELECT setval('sequence_test'::regclass, 99, false);
160 SELECT nextval('sequence_test'::text);
166 DROP SEQUENCE sequence_test;
167 -- renaming sequences
168 CREATE SEQUENCE foo_seq;
169 ALTER TABLE foo_seq RENAME TO foo_seq_new;
170 SELECT * FROM foo_seq_new;
171 sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
172 ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
173 foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f
176 SELECT nextval('foo_seq_new');
182 SELECT nextval('foo_seq_new');
188 SELECT * FROM foo_seq_new;
189 sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
190 ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
191 foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t
194 DROP SEQUENCE foo_seq_new;
195 -- renaming serial sequences
196 ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
197 INSERT INTO serialTest VALUES ('more');
198 SELECT * FROM serialTest;
208 -- Check dependencies of serial and ordinary sequences
210 CREATE TEMP SEQUENCE myseq2;
211 CREATE TEMP SEQUENCE myseq3;
212 CREATE TEMP TABLE t1 (
214 f2 int DEFAULT nextval('myseq2'),
215 f3 int DEFAULT nextval('myseq3'::text)
217 -- Both drops should fail, but with different error messages:
218 DROP SEQUENCE t1_f1_seq;
219 ERROR: cannot drop sequence t1_f1_seq because other objects depend on it
220 DETAIL: default for table t1 column f1 depends on sequence t1_f1_seq
221 HINT: Use DROP ... CASCADE to drop the dependent objects too.
222 DROP SEQUENCE myseq2;
223 ERROR: cannot drop sequence myseq2 because other objects depend on it
224 DETAIL: default for table t1 column f2 depends on sequence myseq2
225 HINT: Use DROP ... CASCADE to drop the dependent objects too.
226 -- This however will work:
227 DROP SEQUENCE myseq3;
229 -- Fails because no longer existent:
230 DROP SEQUENCE t1_f1_seq;
231 ERROR: sequence "t1_f1_seq" does not exist
233 DROP SEQUENCE myseq2;
237 ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
238 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
239 NOTICE: relation "sequence_test2" does not exist, skipping
240 CREATE SEQUENCE sequence_test2 START WITH 32;
241 SELECT nextval('sequence_test2');
247 ALTER SEQUENCE sequence_test2 RESTART WITH 24
248 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
249 SELECT nextval('sequence_test2');
255 SELECT nextval('sequence_test2');
261 SELECT nextval('sequence_test2');
267 SELECT nextval('sequence_test2');
273 SELECT nextval('sequence_test2');
279 ALTER SEQUENCE sequence_test2 RESTART;
280 SELECT nextval('sequence_test2');
286 SELECT nextval('sequence_test2');
292 SELECT nextval('sequence_test2');
298 -- Information schema
299 SELECT * FROM information_schema.sequences WHERE sequence_name IN
300 ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
301 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
302 ORDER BY sequence_name ASC;
303 sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
304 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
305 regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
306 regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
307 regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
308 regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
309 regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
310 regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
314 COMMENT ON SEQUENCE asdf IS 'won''t work';
315 ERROR: relation "asdf" does not exist
316 COMMENT ON SEQUENCE sequence_test2 IS 'will work';
317 COMMENT ON SEQUENCE sequence_test2 IS NULL;
320 SELECT nextval('seq');
332 SELECT setval('seq', 99);
344 CREATE SEQUENCE seq2;
345 SELECT nextval('seq2');
360 ERROR: lastval is not yet defined in this session
361 CREATE USER seq_user;
363 SET LOCAL SESSION AUTHORIZATION seq_user;
364 CREATE SEQUENCE seq3;
365 SELECT nextval('seq3');
371 REVOKE ALL ON seq3 FROM seq_user;
373 ERROR: permission denied for sequence seq3
375 -- Sequences should get wiped out as well:
376 DROP TABLE serialTest, serialTest2;
377 -- Make sure sequences are gone:
378 SELECT * FROM information_schema.sequences WHERE sequence_name IN
379 ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
380 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
381 ORDER BY sequence_name ASC;
382 sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
383 ------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
384 regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES