2 --- test creation of SERIAL column
4 CREATE TABLE serialTest (f1 text, f2 serial);
5 NOTICE: CREATE TABLE will create implicit sequence "serialtest_f2_seq" for serial column "serialtest.f2"
6 INSERT INTO serialTest VALUES ('foo');
7 INSERT INTO serialTest VALUES ('bar');
8 INSERT INTO serialTest VALUES ('force', 100);
9 INSERT INTO serialTest VALUES ('wrong', NULL);
10 ERROR: null value in column "f2" violates not-null constraint
11 SELECT * FROM serialTest;
19 -- basic sequence operations using both text and oid references
20 CREATE SEQUENCE sequence_test;
21 SELECT nextval('sequence_test'::text);
27 SELECT nextval('sequence_test'::regclass);
33 SELECT currval('sequence_test'::text);
39 SELECT currval('sequence_test'::regclass);
45 SELECT setval('sequence_test'::text, 32);
51 SELECT nextval('sequence_test'::regclass);
57 SELECT setval('sequence_test'::text, 99, false);
63 SELECT nextval('sequence_test'::regclass);
69 SELECT setval('sequence_test'::regclass, 32);
75 SELECT nextval('sequence_test'::text);
81 SELECT setval('sequence_test'::regclass, 99, false);
87 SELECT nextval('sequence_test'::text);
93 DROP SEQUENCE sequence_test;
95 CREATE SEQUENCE foo_seq;
96 ALTER TABLE foo_seq RENAME TO foo_seq_new;
97 SELECT * FROM foo_seq_new;
98 sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
99 ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
100 foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f
103 SELECT nextval('foo_seq_new');
109 SELECT nextval('foo_seq_new');
115 SELECT * FROM foo_seq_new;
116 sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
117 ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
118 foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t
121 DROP SEQUENCE foo_seq_new;
122 -- renaming serial sequences
123 ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
124 INSERT INTO serialTest VALUES ('more');
125 SELECT * FROM serialTest;
135 -- Check dependencies of serial and ordinary sequences
137 CREATE TEMP SEQUENCE myseq2;
138 CREATE TEMP SEQUENCE myseq3;
139 CREATE TEMP TABLE t1 (
141 f2 int DEFAULT nextval('myseq2'),
142 f3 int DEFAULT nextval('myseq3'::text)
144 NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
145 -- Both drops should fail, but with different error messages:
146 DROP SEQUENCE t1_f1_seq;
147 ERROR: cannot drop sequence t1_f1_seq because other objects depend on it
148 DETAIL: default for table t1 column f1 depends on sequence t1_f1_seq
149 HINT: Use DROP ... CASCADE to drop the dependent objects too.
150 DROP SEQUENCE myseq2;
151 ERROR: cannot drop sequence myseq2 because other objects depend on it
152 DETAIL: default for table t1 column f2 depends on sequence myseq2
153 HINT: Use DROP ... CASCADE to drop the dependent objects too.
154 -- This however will work:
155 DROP SEQUENCE myseq3;
157 -- Fails because no longer existent:
158 DROP SEQUENCE t1_f1_seq;
159 ERROR: sequence "t1_f1_seq" does not exist
161 DROP SEQUENCE myseq2;
165 CREATE SEQUENCE sequence_test2 START WITH 32;
166 SELECT nextval('sequence_test2');
172 ALTER SEQUENCE sequence_test2 RESTART WITH 24
173 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
174 SELECT nextval('sequence_test2');
180 SELECT nextval('sequence_test2');
186 SELECT nextval('sequence_test2');
192 SELECT nextval('sequence_test2');
198 SELECT nextval('sequence_test2');
204 ALTER SEQUENCE sequence_test2 RESTART;
205 SELECT nextval('sequence_test2');
211 SELECT nextval('sequence_test2');
217 SELECT nextval('sequence_test2');
224 COMMENT ON SEQUENCE asdf IS 'won''t work';
225 ERROR: relation "asdf" does not exist
226 COMMENT ON SEQUENCE sequence_test2 IS 'will work';
227 COMMENT ON SEQUENCE sequence_test2 IS NULL;
230 SELECT nextval('seq');
242 SELECT setval('seq', 99);
254 CREATE SEQUENCE seq2;
255 SELECT nextval('seq2');
270 ERROR: lastval is not yet defined in this session
271 CREATE USER seq_user;
273 SET LOCAL SESSION AUTHORIZATION seq_user;
274 CREATE SEQUENCE seq3;
275 SELECT nextval('seq3');
281 REVOKE ALL ON seq3 FROM seq_user;
283 ERROR: permission denied for sequence seq3