2 --- test creation of SERIAL column
5 CREATE TABLE serialTest (f1 text, f2 serial);
6 NOTICE: CREATE TABLE will create implicit sequence "serialtest_f2_seq" for serial column "serialtest.f2"
8 INSERT INTO serialTest VALUES ('foo');
9 INSERT INTO serialTest VALUES ('bar');
10 INSERT INTO serialTest VALUES ('force', 100);
11 INSERT INTO serialTest VALUES ('wrong', NULL);
12 ERROR: null value in column "f2" violates not-null constraint
14 SELECT * FROM serialTest;
22 -- basic sequence operations using both text and oid references
23 CREATE SEQUENCE sequence_test;
25 SELECT nextval('sequence_test'::text);
31 SELECT nextval('sequence_test'::regclass);
37 SELECT currval('sequence_test'::text);
43 SELECT currval('sequence_test'::regclass);
49 SELECT setval('sequence_test'::text, 32);
55 SELECT nextval('sequence_test'::regclass);
61 SELECT setval('sequence_test'::text, 99, false);
67 SELECT nextval('sequence_test'::regclass);
73 SELECT setval('sequence_test'::regclass, 32);
79 SELECT nextval('sequence_test'::text);
85 SELECT setval('sequence_test'::regclass, 99, false);
91 SELECT nextval('sequence_test'::text);
97 DROP SEQUENCE sequence_test;
99 CREATE SEQUENCE foo_seq;
100 ALTER TABLE foo_seq RENAME TO foo_seq_new;
101 SELECT * FROM foo_seq_new;
102 sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
103 ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
104 foo_seq | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
107 DROP SEQUENCE foo_seq_new;
108 -- renaming serial sequences
109 ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
110 INSERT INTO serialTest VALUES ('more');
111 SELECT * FROM serialTest;
121 -- Check dependencies of serial and ordinary sequences
123 CREATE TEMP SEQUENCE myseq2;
124 CREATE TEMP SEQUENCE myseq3;
125 CREATE TEMP TABLE t1 (
127 f2 int DEFAULT nextval('myseq2'),
128 f3 int DEFAULT nextval('myseq3'::text)
130 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
131 -- Both drops should fail, but with different error messages:
132 DROP SEQUENCE t1_f1_seq;
133 NOTICE: default for table t1 column f1 depends on sequence t1_f1_seq
134 ERROR: cannot drop sequence t1_f1_seq because other objects depend on it
135 HINT: Use DROP ... CASCADE to drop the dependent objects too.
136 DROP SEQUENCE myseq2;
137 NOTICE: default for table t1 column f2 depends on sequence myseq2
138 ERROR: cannot drop sequence myseq2 because other objects depend on it
139 HINT: Use DROP ... CASCADE to drop the dependent objects too.
140 -- This however will work:
141 DROP SEQUENCE myseq3;
143 -- Fails because no longer existent:
144 DROP SEQUENCE t1_f1_seq;
145 ERROR: sequence "t1_f1_seq" does not exist
147 DROP SEQUENCE myseq2;
151 CREATE SEQUENCE sequence_test2 START WITH 32;
152 SELECT nextval('sequence_test2');
158 ALTER SEQUENCE sequence_test2 RESTART WITH 16
159 INCREMENT BY 4 MAXVALUE 22 MINVALUE 5 CYCLE;
160 SELECT nextval('sequence_test2');
166 SELECT nextval('sequence_test2');
172 SELECT nextval('sequence_test2');
179 COMMENT ON SEQUENCE asdf IS 'won''t work';
180 ERROR: relation "asdf" does not exist
181 COMMENT ON SEQUENCE sequence_test2 IS 'will work';
182 COMMENT ON SEQUENCE sequence_test2 IS NULL;
185 SELECT nextval('seq');
197 SELECT setval('seq', 99);
209 CREATE SEQUENCE seq2;
210 SELECT nextval('seq2');
225 ERROR: lastval is not yet defined in this session
226 CREATE USER seq_user;
228 SET LOCAL SESSION AUTHORIZATION seq_user;
229 CREATE SEQUENCE seq3;
230 SELECT nextval('seq3');
236 REVOKE ALL ON seq3 FROM seq_user;
238 ERROR: permission denied for sequence seq3