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 | 31 | 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');
223
-- Information schema
224
SELECT * FROM information_schema.sequences WHERE sequence_name IN ('sequence_test2');
225
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
226
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
227
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
231
COMMENT ON SEQUENCE asdf IS 'won''t work';
232
ERROR: relation "asdf" does not exist
233
COMMENT ON SEQUENCE sequence_test2 IS 'will work';
234
COMMENT ON SEQUENCE sequence_test2 IS NULL;
237
SELECT nextval('seq');
249
SELECT setval('seq', 99);
261
CREATE SEQUENCE seq2;
262
SELECT nextval('seq2');
277
ERROR: lastval is not yet defined in this session
278
CREATE USER seq_user;
280
SET LOCAL SESSION AUTHORIZATION seq_user;
281
CREATE SEQUENCE seq3;
282
SELECT nextval('seq3');
288
REVOKE ALL ON seq3 FROM seq_user;
290
ERROR: permission denied for sequence seq3